System.Plan

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-22 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: merge join nestedloop join sort project filter distinct distinct sort group by or union serial scan index scan TID scan hash scan 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

System.Plan

Technical documentation

» Table of Contents

» Index

EXAMPLE