|
|
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.
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);
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
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.
|