CREATE FORMAT OUTLINE

Description

This statement is used to create a fuzzy outline. You can create a fuzzy outline in two ways: one is by using FORMAT_SQL_TEXT (the original statement with parameters executed by the user), and the other is by using FORMAT_SQL_ID.

Limitations and Considerations

• To create an outline, you must be logged in as the corresponding user.

• When FORMAT_SQL_ID is the same, the outline created by using FORMAT_SQL_TEXT will overwrite the outline created by using FORMAT_SQL_ID. The outline created by using FORMAT_SQL_TEXT has a higher priority. Additionally, seekdb distinguishes different SQL statements by using FORMAT_SQL_ID, which is obtained by taking the MD5 hash of FORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines by using FORMAT_SQL_ID.

  info

  • The matching rules of FORMAT_SQL_TEXT ignore the differences in parameter content, case, and non-syntactic symbols such as spaces and line breaks, and only compare the processed text content.

  • FORMAT_SQL_ID is generated by first formatting FORMAT_SQL_TEXT (removing extra spaces and line breaks, and converting all characters to uppercase or lowercase), and then applying the MD5 hash to the formatted result.

Syntax

/* Create an outline by using FORMAT_SQL_TEXT */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]

/* Create an outline by using FORMAT_SQL_ID */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_sql_id USING HINT hint;

Parameters

Parameter	Description
outline_name	Specifies the name of the fuzzy outline to be created.
OR REPLACE	If you specify OR REPLACE, and the outline to be created already exists, the existing outline will be replaced.
format_stmt	Specifies the original SQL text, that is, the SQL statement or operation to which the outline will be applied.:::info seekdb automatically processes the unformatted SQL text into a formatted SQL text. :::
TO format_target_stmt	Optional. Specifies the target SQL statement to which format_stmt will be converted or applied. This allows the outline to also apply to other SQL statements. You can use the TO format_target_stmt option to declare these SQL statements. Note If you want to fix the plan for a statement with a hint, you should use format_target_stmt to specify the original SQL. When using the TO format_target_stmt clause, the original SQL format_stmt must match format_target_stmt after removing the hint and formatting the SQL.
format_sql_id	The format_stmt obtained after a series of rules are applied to the SQL statement to be bound, and the format_sql_id is the MD5 value calculated based on format_stmt.

Examples

• Create an outline by using FORMAT_SQL_TEXT.

  CREATE FORMAT OUTLINE my_outline ON SELECT * FROM employees WHERE department_id = ?;

• Create an outline by using FORMAT_SQL_ID.

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

References

For more information about how to use outlines, see Plan binding.