 |
» |
|
|
|
When a statement is executed in ISQL or is preprocessed in
an application program, the optimizer attempts to generate the most
efficient path to the desired data. Taking into account the available
indexes, the operations that must be executed, and the clauses in
the predicates that may increase the selectivity of the statement,
the optimizer decides what indexes to use and the proper order of
the needed operations. The result of this evaluation process is
an access plan produced by the optimizer. In most cases, the optimizer chooses the best plan. But, there
are times when you may want to display the access plan chosen by
the optimizer. You may then evaluate that plan in light of your
specific knowledge of the database and decide if the optimizer has generated
the optimum access plan for your situation. If you want to override the access plan chosen by the optimizer,
issue the SETOPT statement. The statements used to generate and display the access plan
are the GENPLAN statement and a SELECT on the pseudotable SYSTEM.PLAN. Generating a Plan | |
Suppose you want to generate the access plan for the query
shown below. isql=> GENPLAN FOR
> SELECT p.PartName, p.PartNumber, v.VendorName,
> s.UnitPrice, i.QtyOnHand
> FROM PurchDb.Parts p, PurchDB.Inventory i,
> PurchDB.SupplyPrice s, PurchDB.Vendors v
> WHERE p.PartNumber = i.PartNumber
> AND s.PartNumber = p.PartNumber
> AND s.VendorNumber = v.VendorNumber
> AND p.PartNumber = '1123-P-01'; |
The access plan will then be placed in the system pseudotable,
SYSTEM.PLAN, but will not be displayed until you do a SELECT from SYSTEM.PLAN. You can also generate the access plan
for a query that is stored in the database as a stored section.
For example: isql=> GENPLAN FOR MODULE SECTION MyModule(10); |
Displaying a Query Access Plan | |
To display the access plan generated by the optimizer, showing
the columns in the order most useful to you, execute the following
statement: isql=> SELECT Operation, TableName, IndexName, QueryBLock, Step, Level
> FROM System.Plan;
SELECT Operation, TableName, IndexName, QueryBlock, Step, Level FROM System.Plan
-----------------+------------+----------------+-----------+--------+--------
OPERATION |TABLENAME |INDEXNAME |QUERYBLOCK |STEP |LEVEL
-----------------+------------+----------------+-----------+--------|--------
index scan |INVENTORY |INVPARTNUMINDEX | 1| 1| 4
index scan |PARTS |PARTNUMINDEX | 1| 2| 4
merge join | | | 1| 3| 3
serial scan |SUPPLYPRICE | | 1| 4| 3
nestedloop join | | | 1| 5| 2
index scan |VENDORS |VENDORNUMINDEX | 1| 6| 2
nestedlopp join | | | 1| 7| 1 -----------------------------------------------------------------------------
Number of rows selected is 7
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] >r Interpreting a Display | |
The information from the columns in SYSTEM.PLAN helps you
to understand the access plan generated by the optimizer. The columns
are discussed in the order most useful to you. - OPERATION
shows each operation being executed to obtain the
data. Because your greatest concern is usually whether indexes are
being used effectively, you should look at this column first. For
each index scan operation, indexes are being used to access the
data. If there is no limiting predicate in the WHERE clause
of the statement, or if the predicate will cause the selection of
a large percentage of the rows from the table, a serial scan will
be chosen instead of an index scan. When a join is specified, you can look at the join chosen
to see if it is the most appropriate type of join, considering the
specific data in your database. For more information, see the "Understanding Data
Access Paths" section of Chapter 2 “Using ALLBASE/SQL” - TABLENAME
shows the table upon which an operation is being
executed. Thus, you can see the tables for which indexes are being
used, and the tables which are participating in various joins. - INDEXNAME
shows which specific index is being used to access
data in a particular table. This may be useful if multiple indexes
exist for a given table. - QUERYBLOCK
shows the block in which a given operation is taking
place. A simple statement will have only one query block. More complex
statements will be broken into additional blocks to simplify processing. - STEP
shows the order in which operations are executed
within a given queryblock. From this information you can determine
the order of operations. - LEVEL
shows the hierarchy of the operations so you can
easily graph the operations as an execution tree. This is normally
necessary only when your HP Service Representative is evaluating
a query.
|
