Provides a mechanism for grouping the query plan steps of multi-step queries, such as queries requiring worktables, and queries computing aggregate values.
(plan query_step1 ... query_stepN )
specify the abstract plan steps for the execution of each step in the query.
A derived table.
select max(c11) from t1 group by c12
( plan ( store Worktab1 ( t_scan t1 ) ) ( t_scan ( work_t Worktab1 ) ) )
Returns a vector aggregate. The first operand of the plan operator creates Worktab1 and specifies a table scan of the base table. The second operand scans the worktable to return the results.
select max(c11) from t1
( plan ( t_scan t1 ) ( ) )
Returns a scalar aggregate. The last derived table is empty, because scalar aggregates accumulate the result value in an internal variable rather than a worktable.
select * from t1 where c11 = (select count(*) from t2)
( plan ( i_scan i_c21 (table t2 ( in_subq 1) ) ) ( i_scan i_c11 t1 ) )
Specifies the execution of a materialized subquery.
create view v3 as select distinct * from t3
select * from t1, v3 where c11 = c31
( plan ( store Worktab1 ( t_scan (table t3 (in_view v3 ) ) ) ) ( nl_g_join ( t_scan t1 ) ( t_scan ( work_t Worktab1 ) ) ) )
Specifies the execution of a materialized view.
Tables are accessed in the order specified, with the specified access methods.
The plan operator is required for multistep queries, including:
Queries that generate worktables, such as queries that perform sorts and those that compute vector aggregates
Queries that compute scalar aggregates
Queries that include materialized subqueries
An abstract plan for a query that requires multiple execution steps must include operands for each step in query execution if it begins with the plan keyword. Use the hints operator to introduce partial plans.