Purpose
This statement is used to add new binding rules, including execution plan binding and throttling rules, to an existing outline. It only supports modifying outlines created using the SQL_TEXT option.
Syntax
ALTER OUTLINE outline_name ADD stmt [ TO target_stmt ]
Parameters
| Parameter | Description |
|---|---|
| outline_name | Specifies the name of the outline to be modified. |
| stmt | Typically, a DML statement that includes hints and original parameters. |
| TO target_stmt | If TO target_stmt is not specified, it means that if the SQL statement accepted by the database, after parameterization, matches the stmt statement with hints removed, the SQL statement will be bound to the execution plan generated by the hint in stmt. If you want to fix the execution plan for a statement that contains hints, you need to use TO target_stmt to specify the original SQL statement. Note When using target_stmt, it is strictly required that stmt and target_stmt must be completely matched after removing the hints. |
Examples
Add a throttling rule using
ALTER OUTLINE.obclient> ALTER OUTLINE ol_1 ADD SELECT /*+max_concurrent(1)*/ * FROM t1 WHERE c1 = 1 and c2 = ?; obclient> ALTER OUTLINE ol_1 ADD SELECT /*+max_concurrent(1)*/ * FROM t1 WHERE c1 = ? and c2 = 1;Add an execution plan using
ALTER OUTLINE.obclient> CREATE OUTLINE ol_2 ON SELECT /*+max_concurrent(1)*/ * FROM t1,t2 WHERE t1.c1 = 1; obclient> ALTER OUTLINE ol_2 ADD SELECT /*+use_nl(t2)*/ * FROM t1,t2 WHERE t1.c1 = 1;
Considerations
Only one execution plan can be specified for the same
outline_name. If an execution plan is specified using theCREATE OUTLINEstatement, it cannot be added again when executing theALTER OUTLINEstatement.Similar to
CREATE OUTLINE, you cannot specify both throttling rules and execution plans at the same time when usingALTER OUTLINE.