CREATE FORMAT OUTLINE

Purpose

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

Limitations and considerations

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

• If the FORMAT_SQL_ID values are the same, the outline created by using the FORMAT_SQL_TEXT method will overwrite the one created by using the FORMAT_SQL_ID method, because the priority of the FORMAT_SQL_TEXT method is higher. OceanBase Database uses FORMAT_SQL_ID to distinguish between different SQL statements, which is generated by using the MD5 hash encryption of the FORMAT_SQL_TEXT value after removing redundant spaces and line breaks and converting all characters to upper or lower case. We recommend that you use FORMAT_SQL_ID to bind an outline in a production system.

  Note

  • The matching rule of FORMAT_SQL_TEXT ignores differences in parameter content, case, spaces, and line breaks, and compares only the text content after these differences are removed.
  • FORMAT_SQL_ID is generated by applying MD5 hash encryption to the result after FORMAT_SQL_TEXT is formatted (extra spaces and line breaks are removed, and all characters are converted to upper or lower case).

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 the FORMAT_SQL_TEXT value.

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

• Create an outline by using the FORMAT_SQL_ID value.

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

References

For more information about outlines, see Plan binding.