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

A 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 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

See also

hints