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

• To create an outline, you must be logged in as the corresponding user.

• If 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 a higher priority. In addition, OceanBase Database distinguishes different SQL statements by using FORMAT_SQL_ID, which is obtained by taking the MD5 hash of FORMAT_SQL_TEXT. In a production system, we recommend that you bind an outline by using FORMAT_SQL_ID.

  Note

  • The matching rules of FORMAT_SQL_TEXT ignore the differences in parameter content, case, and non-syntactic symbols such as spaces and line breaks. It only compares the processed text content.
  • FORMAT_SQL_ID is generated by 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(tbl1 idx_col2)*/;
  

References

For more information about using outlines, see Plan binding.