ALLBASE/SQL Reference Manual (36216-90216)

Chapter 3 135
SQL Queries
Using GENPLAN to Display the Access 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.