ALLBASE/SQL Reference Manual (36216-90216)
134 Chapter3
SQL Queries
Using GENPLAN to Display the Access Plan
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;