CREATE FORMAT OUTLINE

Purpose

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

Limitations and considerations

• You must log in as the corresponding user to create an outline.

• If you create an outline by using FORMAT_SQL_TEXT, the outline created by using FORMAT_SQL_TEXT will override the one created by using FORMAT_SQL_ID if they have the same FORMAT_SQL_ID. In addition, OceanBase Database uses FORMAT_SQL_ID to distinguish different SQL statements, and FORMAT_SQL_ID is generated by applying the MD5 hash encryption to the result of formatting FORMAT_SQL_TEXT (removing redundant spaces and line breaks, and converting all characters to uppercase or lowercase). We recommend that you bind an outline by using FORMAT_SQL_ID in a production system.

  Note

  • The matching rule for FORMAT_SQL_TEXT ignores differences in parameter content, case, spaces, and line breaks. It only compares the text content after these differences are removed.
  • FORMAT_SQL_ID is generated by applying the MD5 hash encryption to the result of formatting FORMAT_SQL_TEXT (removing redundant spaces and line breaks, and converting all characters to uppercase or lowercase).

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

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(tbl1 idx_col2)*/;
  

References

For more information about outlines, see Plan binding.