Using the plan clause

You can use the plan clause with the following SQL statements to specify the plan to use to execute the query:

• select

• insert...select

• delete

• update

• if

• while

• return

This example specifies the plan to use to execute the query:

select avg(price) from titles
    plan
“(scalar_agg
    (i_scan type_price_ix titles
)”

When you specify an abstract plan for a query, the query is executed using the specified plan. If you have showplan enabled, this message is printed:

Optimized using the Abstract Plan in the PLAN clause.

When you use the plan clause with a query, any errors in the SQL text, the plan syntax, and any mismatches between the plan and the SQL text are reported as errors. For example, this plan uses the wrong abstract plan operator for the query:

/* wrong operator! */
select * from t1,t2
where c11 = c21
plan
“(union
 (t_scan t1)
 (t_scan t2)
)"

This plan returns the following message:

Abstract Plan (AP) Warning: An error occurred while applying the AP:
(union (t_scan t1) (t_scan2))
to the SQL query:
select * from t1, t2
where c11 = c21
Failed to apply the top operator ‘union’ of the following AP fragment:
(union (t_scan t1) (t_scan t2))
The query contains no union that matches the ‘union’ AP operator at this point.
The following template can be used as a basis for a valid AP:
(also_enforce (join (also_enforce (scan t1)) (also_enforce (scan t2)))
)
The optimizer will complete the compilation of this query; the query will be executed normally.

Plans specified with the plan clause are saved in sysqueryplans only if plan capture is enabled. If a plan for the query already exists in the current capture group, enable replace mode to replace an existing plan.