HP 3000 Manuals HP 3000 Manuals
Using GENPLAN to Display the Access Plan
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';
[REV BEG]
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);
[REV END]
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
nestedloop 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 the chapter "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.