Informix query optimizer

 

The Informix Query Optimizer

• The query optimizer attempts to determine the most efficient way to execute a SQL statement

 • Examines every possible method to implement the query and selects the least costly method

• It is Dynamic, so when data changes the same SQL can select a better path

How Optimizes the SQL statement to determine the best access method

– Which table to read first….

– Which index to use first…

– Which filter to use first…

 

How to Turn ON Query Explain? To See the SQL Optimizer Query Plan

After executes below queries with “SET EXPLAIN” it is creates a output file called “SQL_EXPLAIN.OUT”

Method#1

                SET EXPLAIN ON -

                                Display the query plan that optimizer chooses, then execute the query.

                                Example:

                                                set explain on;

                                                select cust_name,cust_addrs from customer where cust_code=”C001”;

Method#2

                SET EXPLAIN ON FILE TO [“path/to/filename”]

                                Save the query plan into a specific file also execute the query.

Can useful when programming unit testing.

Method#3

                SET EXPLAIN ON AVOID_EXECUTE

                                Display the query plan that optimizer chooses, but do not execute the query.

Can useful when a query takes hours to run/for time consuming queries.

“SQL_EXPLAIN.OUT” file results explanation

A. Show SQL/SELECT statement.

B. Query cost

C. Number of Rows Expected to return

D. Order to access tables during execution.

E. Access Plan:

                SEQUENTIAL Scan - read all rows in sequence

                INDEX PATH - Scan 1 or more indexes

                AUTO INDEX PATH: Optimizer suggest to create an indexes.

F. Query Statistics

Shows tables map, estimated row count and estimated query costs step by step.

                This will helps to find out in which part of the query cost is high.

                So can change the query based on results output.