CREATE FORMAT OUTLINE

Purpose

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

• You must execute the statement under 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 higher priority. In addition, OceanBase Database distinguishes different SQL statements by using FORMAT_SQL_ID, which is obtained by encrypting the FORMAT_SQL_TEXT with the MD5 algorithm. In a production system, it is recommended to bind an outline by using FORMAT_SQL_ID.

  Note

  • The matching rules of FORMAT_SQL_TEXT ignore 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 encryption 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

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 using outlines, see Plan binding.