The Explain Plan
The Explain Plan is a formatted report detailing how Omnidex will process
a query. It provides warnings and suggestions, optimization techniques
used and step-by-step details explaining exactly how the query was processed.
The Explain Plan is available through the Omnidex utility, ODXSQL.
1-Pass Optimization
Deferred Optimization
The Explain Plan can be generated in several ways:
- Execute a select statement then execute the EXPLAIN command after
the rows have been displayed.
>select * from customers where company='systems'
...
>explain
- Execute the EXPLAIN command with a select statement as an argument.
>explain select * from customers where company='systems'
- Execute a select statement with the EXPLAINONLY option then execute
the EXPLAIN command. The rows will not be displayed.
>select * from customers where company='systems'
with explainonly
>explain
- Set AUTOEXPLAIN ON then execute a select statement. The rows will
not be displayed.
>set autoexplain on
>select * from customers where company='systems'
1-Pass Optimization
>SELECT COMPANY, STATE FROM CUSTOMERS WHERE CUSTOMER_NO
IN
(SELECT CUSTOMER_NO FROM ORDERS WHERE STATUS ='back')
...
>EXPLAIN
The Explain Plan is divided into two sections: SUMMARY and DETAILS.
----------------------------------- SUMMARY -----------------------------------
Select COMPANY, \
STATE \
from CUSTOMERS \
where CUSTOMER_NO in \
(select CUSTOMER_NO \
from ORDERS \
where STATUS = 'back')
Version: 4.0 Build 8G (Compiled Jan 30 2004 12:50:07)
Optimization: MDKQUAL
----------------------------------- DETAILS -----------------------------------
Qualify (CUSTOMERS)ORDERS where STATUS = 'back' on 1 with NOAUTORESET
Join ORDERS using CUSTOMER_NO to (CUSTOMERS)CUSTOMERS using CUSTOMER_NO
on 1 \
with NOAUTORESET,COUNT
Fetchkeys $ROWID 1000 at a time on 1
Retrieve CUSTOMERS using $ROWID = $ODXID
Return CUSTOMERS.COMPANY, CUSTOMERS.STATE
-------------------------------------------------------------------------------
The above example shows a nested query against the Orders sample database.
The SUMMARY section displays the SELECT statement formatted for clarity,
as well as Omnidex version information and the type of optimization used
in this query. MDKQUAL means that the query was processed as an MDK qualification.
The DETAILS section explains each step taken to process this query. This
first line "Qualify (CUSTOMERS)ORDERS ..." means the criteria
of the inner query "STATUS = 'back' " was processed within the
RS domain of the indexes. "on 1" means the query was processed
on cursor 1. NOAUTORESET is a default setting that causes ODXSQL to retain
the qualified ID list for further refinement. Then the qualified rows
were joined to the CUSTOMERS table using the link field CUSTOMER_NO. Fetchkeys
$ROWID ... means Omnidex "fetched" the rowids of the qualified
records from the ORDERS table that have matching CUSTOMERS records. "Retrieve
CUSTOMERS ... " gets the rowids of the corresponding CUSTOMERS records.
All of the processing up to this point has been entirely within the Omnidex
indexes. The next two steps, "Retrieve CUSTOMERS..." and "Return
CUSTOMERS..." retrieves and returns the select-list items. This is
the last step and the only step in which the database has been touched,
meaning the query was processed with 1-pass optimization.
Deferred Optimization
>SELECT ACCT, COMPANY FROM PROSPECTS WHERE ACCT IN
(SELECT DISTINCT ACCT FROM ORDERS WHERE SOURCE = 1)
...
>EXPLAIN
The Explain Plan is divided into two sections: SUMMARY and DETAILS.
----------------------------------- SUMMARY -----------------------------------
Select ACCT, \
COMPANY \
from PROSPECTS \
where ACCT in \
(select distinct ACCT \
from ORDERS \
where SOURCE = 1)
Version: 4.0 Build 8G (Compiled Jan 30
2004 12:50:07)
Optimization: MDKQUAL
Warnings: UNOPTIMIZED_CRITERIA, UNOPTIMIZED_SORT, SEQUENTIAL_SCAN
Notes: Filter on column SOURCE will not be
optimized because there is
not an MDK index
installed on the column
----------------------------------- DETAILS -----------------------------------
Processing subquery 1 into {SQ1}
Retrieve ORDERS sequentially
Filter ORDERS.SOURCE = 1
Pass to queue {1} [ORDERS.ACCT]
Sort {1} for DISTINCT [ORDERS.ACCT ASC]
Retrieve {1} sequentially
Returning subquery 1 into {SQ1}
Qualify (PROSPECTS)PROSPECTS where ACCT = '{SQ1}' on 1 with
NOAUTORESET
Fetchkeys $ROWID 1000 at a time on 1
Retrieve PROSPECTS using $ROWID = $ODXID
Return PROSPECTS.ACCT, PROSPECTS.COMPANY
-------------------------------------------------------------------------------
Notice that the SUMMARY section contains "Warnings" and "Notes".
These indicate that there may be a problem optimizing some part of the
query. Also notice that "Optimization" says "MDKQUAL",
indicating that at least part of the query is optimized.
The first part of the DETAILS section shows the processing of the inner
query (subquery), which is shown as {SQ1}. Because the column "SOURCE",
which is in a criteria predicate in the inner query, is not installed
with an MDK index, the inner query will not be optimized. Therefore, all
of the ORDERS records are retrieved then filtered to match the criteria
"SOURCE = 1". The filtered rows are passed to a queue where
they are sorted ascending by ACCT. The sorted ORDERS records are then
retrieved and returned into a buffer {SQ1} for use as criteria for the
outer query.
The last four steps are fully optimized because the ACCT column in the
PROSPECTS table is installed with an MDK index. The PROSPECTS records
are qualified using the ACCT numbers from the inner query, and the records
are returned.
The problem encountered in this example can be fixed by installing an
MDK index on the ACCT column in the ORDERS table.
Top
|
