CREATE OUTLINE

Description

This statement is used to create an outline. You can create an outline in two ways: using SQL_TEXT (the original statement with parameters executed by the user) or using SQL_ID.

info

To create an outline, you must execute the statement in the corresponding database.

When SQL_ID is the same, the outline created using SQL_TEXT will overwrite the outline created using SQL_ID. The outline created using SQL_ID has a higher priority.

Additionally, SeekDB distinguishes different SQL statements based on SQL_ID, which is obtained by taking the MD5 hash of SQL_TEXT. Even if the SQL text is the same but has an extra newline or tab, the resulting SQL_ID will be different. In a production system, it is recommended to bind outlines using SQL_ID.

Syntax

• Create an outline using SQL_TEXT.

  CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]

• Create an outline using SQL_ID.

  CREATE OUTLINE outline_name ON sql_id USING HINT hint;

Parameter Description

Parameter	Description
outline_name	Specifies the name of the outline to be created.
OR REPLACE	If specified, the existing outline with the same name will be replaced when creating a new one.
stmt	Generally, a DML statement with hints and original parameters.
TO target_stmt	If TO target_stmt is not specified, it means that if the parameterized SQL accepted by the database is the same as stmt after removing the hint parameters, the SQL will be bound to the hint in stmt to generate an execution plan. If you want to fix the plan for a statement with hints, you need to use TO target_stmt to specify the original SQL. Note When using target_stmt, it is strictly required that stmt and target_stmt match exactly after removing the hints.
sql_id	If the SQL statement corresponding to sql_id already has hints, the hints specified when creating the outline will overwrite all hints in the original statement.
hint	The format is /*+ xxx */.

Examples

• Create an outline using SQL_TEXT.

  CREATE OUTLINE otl_idx_c2
  ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;

• Create an outline using SQL_ID.

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