plan

Description

Provides a mechanism for grouping the query plan steps of multi-step queries, such as queries requiring worktables, and queries computing aggregate values.

Syntax

(plan
          query_step1
          ...
          query_stepN
)

Parameters

query_step1...query_stepN –: specify the abstract plan steps for the execution of each step in the query.

Returns

An abstract plan derived table.

Examples

Example 1

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.

Example 2

select max(c11) from t1

( plan
    ( t_scan t1 )
    ( )
)

Returns a scalar aggregate. The last abstract plan derived table is empty, because scalar aggregates accumulate the result value in an internal variable rather than a worktable.

Example 3

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.

Example 4

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.

Usage

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.