CREATE FORMAT OUTLINE

Purpose

You can use this statement to create a fuzzy outline based on FORMAT_SQL_TEXT or FORMAT_SQL_ID. FORMAT_SQL_TEXT is the original statement that is executed by the user and that has arguments.

Limitations and considerations

• To create an outline, you must log in to the database as the corresponding user.

• When two outlines have the same FORMAT_SQL_ID, the outline created based on FORMAT_SQL_TEXT overwrites the one created based on FORMAT_SQL_ID, because the former has a higher priority. In addition, OceanBase Database distinguishes SQL statements by FORMAT_SQL_ID, which is the MD5 hash value of FORMAT_SQL_TEXT. In the actual production system, we recommend that you use FORMAT_SQL_ID to bind an outline.

  Note

  An outline created based on

  • FORMAT_SQL_TEXT will ignore the differences in parameter content, letter cases, and non-syntactic symbols such as spaces and line breaks, and compares only the content after processing.
  • FORMAT_SQL_ID is the MD5 hash value of FORMAT_SQL_TEXT after excess spaces and line breaks are removed and all letters are converted into uppercase or lowercase letters.

Syntax

/* Create an outline based on FORMAT_SQL_TEXT. */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]

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

Parameters

Examples

• Create an outline based on FORMAT_SQL_TEXT.

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

• Create an outline based on 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.