Abstract plans can be full plans, describing all query processing steps and options, or they can be partial plans. A partial plan might specify that an index is to be used for the scan of a particular table, without specifying other access methods. For example:
select t1.c11, t2.c21 from t1, t2, t3 where t1.c11 = t2.c21 and t1.c11 = t3.c31 plan “(i_scan t3_c31_ix t3)”
The full abstract plan includes:
The join type, nl_join for nested-loop joins, m_join for merge joins, or h_join for hash joins.
The join order.
The type of scan, t_scan for table scan or i_scan for index scan.
The name of the index chosen for the tables that are accessed via an index scan.
The scan properties: the parallel degree, I/O size, and cache strategy for each table in the query.
The abstract plan for the query above specifies the join order, the access method for each table in the query, and the scan properties for each table:
select t1.c11, t2.c21
from t1, t2, t3
where t1.c11 = t2.c21
and t1.c11 = t3.c31
plan
“(i_scan t3_c31_ix t3)”
(nl_join ( nl_join
( t_scan t2 )
( i_scan t1_c11_ix t1 )
)
( i_scan t3_c31_ix t3 )
)
( prop t3
( parallel 1 )
( prefetch 16 )
( lru )
)
( prop t1
( parallel 1 )
( prefetch 16 )
( lru )
)
( prop t2
( parallel 1 )
( prefetch 16 )
( lru )
)
If the abstract plan dump mode is on, the query text and the abstract plan pair are saved in sysqueryplans:
select t1.c11, t2.c21 from t1, t2, t3 where t1.c11 = t2.c21 and t1.c11 = t3.c31 plan “(i_scan t3_c31_ix t3)”
