All Products
Search
Document Center

CREATE OUTLINE

Last Updated: Jun 18, 2021

Description

This statement creates an outline. You can create an outline by using the two methods: SQL_TEXT and SQL_ID. SQL_TEXT is an original statement that contains parameters and is executed by a user.

Notice

To create an outline, you must use the corresponding user for execution.

Syntax

  • Create an outline by using SQL_TEXT.

CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
  • Create an outline by using SQL_ID.

CREATE OUTLINE outline_name ON sql_id USING HINT  hint;

Parameter description

Parameter

Description

outline_name

The name of the outline to be created.

OR REPLACE

If the outline to be created already exists after you specify OR REPLACE, the original outline is replaced.

stmt

The value is generally a DML statement that contains hints and original parameters.

TO target_stmt

Assume that you do not specify TO target_stmt and the SQL statement accepted by the database is parameterized. If the parameterized SQL statement is the same as the parameterized text of stmt from which the hint is removed, the SQL statement is bound to the hint in stmt to generate an execution plan. If you need to generate a fixed plan for the statement that contains a hint, you must use TO target_stmt to specify the original SQL statement.

Notice

When target_stmt is used, it is strictly required that stmt exactly matches target_stMT from which the hint is removed.

sql_id

If the SQL statement that corresponds to sql_id has a hint, the hint that you specify when you create the outline overwrite all the hints in the original statement.

hint

The format is /*+ xxx */.

Examples

  • Create an outline by using SQL_TEXT.

CREATE OUTLINE otl_idx_c2 
       ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;
  • Create an outline by using SQL_ID.

CREATE OUTLINE otl_idx_c2 
ON 'ED570339F2C856BA96008A29EDF04C74'
USING HINT /*+ index(t1 idx_c2)*/ ;

Considerations

An outline created by using SQL_TEXT overwrites an outline created by using SQL_ID. SQL_TEXT. The outline created by using SQL_TEXT has the higher precedence.