 |
» |
|
|
|
SYSTEM.PLAN is a pseudotable which displays the access plan
generated by the optimizer for a SELECT, UPDATE or DELETE statement
processed by the GENPLAN statement. Information is displayed for only
a single statement at a time. To display an access plan, you must first process a
statement of the above type with the GENPLAN
statement as in the following example:
isql=> GENPLAN FOR SELECT * FROM Purchdb.Parts;
|
To display the access plan, issue the following statement within the same
transaction:
isql=> SELECT * FROM System.Plan;
|
Table 8-23 System.Plan |
Column Name | Type | Length | Description |
|---|
| QUERYBLOCK | INTEGER | 4 | Queryblock in which operation is executed
| | STEP | INTEGER | 4 | Sequence within the query block
in which operation is executed at run time
| | LEVEL | INTEGER | 4 | Level of operation within the run tree
| | OPERATION | CHAR | 20 | Type of Operation:
block scan (block number)
| | TABLENAME | CHAR | 20 | Table upon which operation is executed | | OWNER | CHAR | 20 | Owner of the table
| | INDEXNAME | CHAR | 20 | Name of index used for operation
|
Example | |
isql=> GENPLAN FOR
> SELECT *
> FROM Purchdb.Parts
> WHERE Partnumber =
> (SELECT Partnumber
> FROM PurchDB.SupplyPrice sp, PurchDB.Vendors v
> WHERE v.VendorName = 'Pro-Litho Inc.'
> AND sp.UnitPrice <= 200.00
> AND sp.VendorNumber = v.VendorNumber);
isql=> SELECT * FROM System.Plan;
select * FROM System.Plan;
-----------+-----------+-----------+--------------------+------------------
QUERYBLOCK |STEP |LEVEL |OPERATION |TABLENAME
-----------+-----------+-----------+--------------------+------------------
1| 1| 3|serial scan |VENDORS
1| 2| 3|serial scan |SUPPLYPRICE
1| 3| 2|nestedloop join |
2| 1| 1|index scan |PARTS
---------------------------------------------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > r
+--------------------+----------------
|OWNER |INDEXNAME
+--------------------+----------------
|PURCHDB |
|PURCHDB |
| |
|PURCHDB |PARTNUMINDEX
--------------------------------------
Number of rows selected is 4
U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>, or e[nd] > e
|
|
