CREATE FORMAT OUTLINE

Purpose

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

Limitations and considerations

• To create an outline, you must execute the statement under the corresponding user.

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

  Note

  • The matching rules for FORMAT_SQL_TEXT ignore changes in parameters, case sensitivity, and non-syntactic symbols such as spaces and line breaks. For an IN expression, normalization is performed, and only the processed text content is compared.
  • 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 using FORMAT_SQL_TEXT */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]

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

Parameters

Examples

• Create an outline using FORMAT_SQL_TEXT.

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

• Create an outline using FORMAT_SQL_ID.

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

References

For more examples on using outlines, see Plan binding.