ALLBASE/SQL Reference Manual (36216-90216)

Chapter 11 433
SQL Statements E - R
GENPLAN
procedure, with no regard to any branching or looping constructs in the procedure.
• Multiple sessions may issue the GENPLAN statement at the same time because each
session has its own individual copy of SYSTEM.PLAN.
• See the section "Using GENPLAN to Display the Access Plan" in the "SQL Queries"
chapter for information on how to interpret the plan.
• You cannot use GENPLAN with the SYSTEM or CATALOG views.
Authorization
To execute GENPLAN, you must have DBA authority or the appropriate combination of
SELECT, UPDATE, or DELETE authorities for the tables and views accessed by the included
SQL statement. In the case of views, you must have the appropriate authorities for all
underlying views and base tables, as well.
Examples
1. Interactive SQL statement for the following query:
>isql=> SELECT PartName, VendorNumber, UnitPrice
> FROM Purchdb.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = '1123-P-01';
Generate the Plan:
isql=> GENPLAN FOR
> SELECT partname, vendornumber, UnitPrice
> FROM PurchDB.Parts p, PurchDB.SupplyPrice sp
> WHERE p.PartNumber = sp.PartNumber
> AND p.PartNumber = '1123-P-01';
Display the Plan:
isql=> SELECT * FROM System.Plan;
SELECT * FROM System.Plan;
-----------+-----------+-----------+--------------------+---------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+---------------
1| 1| 2|index scan |PARTS
1| 2| 2|serial scan |SUPPLYPRICE
1| 3| 1|nestedloop join |
------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >r
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |PARTNUMINDEX
|PURCHDB |
| |
------------------------------------------------------------------------
Number of rows selected is 3
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int], <n>, or e[nd] >e