Reading Query Plan from the PLAN Table

Once plan generation has been turned on and a command has been prepared, one or more rows in the SYS.PLAN table store the plan for the command. The number of rows in the table depends on the complexity of the command. Each row has seven columns, as described in System Tables and Views in the Oracle TimesTen In-Memory Database System Tables and Views Reference.

The following example generates a query plan with the following query:

Command> SELECT COUNT(*) FROM t1, t2, t3 
WHERE t3.b/t1.b > 1 
AND t2.b <> 0 
AND t1.a = -t2.a
AND t2.a = t3.a

The optimizer generates the five SYS.PLAN rows shown in the following table. Each row is one step in the plan and reflects an operation that is performed during query processing.

Step	Level	Operation	TblNames	IXName	Pred	Other Pred
1	3	TblLkRangeScan	t1	IX1	N/A	N/A
2	3	TblLkRangeScan	t2	IX2(D)	N/A	t2.b <> 0
3	2	MergeJoin	N/A	N/A	t1.a = -t2.a	N/A
4	2	TblLkRangeScan	t3	IX3(D)	N/A	N/A
5	1	MergeJoin	N/A	N/A	t2.a = t3.a	t3.b / t1.b > 1

For details about each column in the SYS.PLAN table, see Describing the PLAN Table Columns.