Purpose
This statement applies only to outlines created using SQL_TEXT. It is used to add binding outlines and throttling rules.
Syntax
ALTER OUTLINE outline_name ADD stmt [ TO target_stmt ]
Parameters
| Parameter | Description |
|---|---|
| outline_name | The name of the outline to be modified. |
| stmt | Generally, a DML statement that contains hints and original parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the system will generate an execution plan based on the hints in the stmt if the SQL statement without hints in the database is the same as the SQL statement without hints in stmt; if you want to specify a fixed execution plan for a hint-containing statement, you must specify TO target_stmt to indicate the original SQL statement. Notice When you use target_stmt, the stmt must exactly match the target_stmt after hints are removed. |
Examples
Use the
ALTER OUTLINEstatement to add a throttling rule.obclient> CREATE TABLE tbl1 (col1 NUMBER, col2 NUMBER); obclient> CREATE OUTLINE outline1 ON SELECT /*+max_concurrent(1)*/ * FROM tbl1 WHERE col1 =1 AND col2 = 1; obclient> ALTER OUTLINE outline1 ADD SELECT /*+max_concurrent(1)*/ * FROM tbl1 WHERE col1 =1 AND col2 = ?; obclient> ALTER OUTLINE outline1 ADD SELECT /*+max_concurrent(1)*/ * FROM tbl1 WHERE col1 =? AND col2 = 1;Multiple throttling rules can be created for the same signature.
obclient> CREATE TABLE tbl2 (col1 NUMBER, col2 NUMBER); obclient> CREATE OUTLINE olt_2 ON SELECT /*+max_concurrent(1)*/ c2,c1 FROM t1 WHERE c1 > 3 AND c2 = 1; obclient> ALTER OUTLINE olt_2 ADD SELECT /*+max_concurrent(2)*/ c2,c1 FROM t1 WHERE c1 > ? AND c2 = 1; obclient> ALTER OUTLINE olt_2 ADD SELECT /*+max_concurrent(2)*/ c2,c1 FROM t1 WHERE c1 > ? AND c2 = 2; obclient> ALTER OUTLINE olt_2 ADD SELECT /*+max_concurrent(0)*/ c2,c1 FROM t1 WHERE c1 > ? AND c2 = 3;
Considerations
You can specify only one execution plan for a single
outline_name. If an execution plan is specified foroutline_nameusing theCREATE OUTLINEstatement, you cannot add another execution plan for the sameoutline_nameusing theALTER OUTLINEstatement.You cannot specify a throttling rule and an execution plan at the same time during an
ALTER OUTLINEoperation, just as you cannot specify a throttling rule and an execution plan at the same time during aCREATE OUTLINEoperation.During an
ALTER OUTLINEoperation, bothoutline_nameandsignaturemust be matched.