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';

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

   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.

Using GENPLAN to Display the Access Plan

Technical documentation

» Table of Contents

» Index

Generating a Plan

Displaying a Query Access Plan

Interpreting a Display