CREATE FORMAT OUTLINE|V4.4.2|OceanBase Database| docs|Distributed Database

CREATE FORMAT OUTLINE

Last Updated：2026-04-02 06:23:57 Updated

Purpose

This statement is used to create a fuzzy outline. A fuzzy outline can be created in two ways: one is by using FORMAT_SQL_TEXT (the parameterized original statement executed by the user), and the other is by using FORMAT_SQL_ID.

Limitations and considerations

To create an outline, 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 a higher priority. Additionally, OceanBase Database distinguishes different SQL statements by using FORMAT_SQL_ID, which is generated by taking the MD5 hash of FORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines by using FORMAT_SQL_ID.
Note
- The matching rules of FORMAT_SQL_TEXT ignore changes in parameters, case differences, and non-syntactic symbols such as spaces and line breaks. For an IN expression, normalization is performed, and only the text content after these processing steps 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 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

Parameter	Description
outline_name	The name of the fuzzy outline to be created.
OR REPLACE	If you specify `OR REPLACE`, and the outline to be created already exists, the existing outline will be replaced.
format_stmt	The original SQL statement, which is the SQL statement or operation to which the outline will be applied. Note OceanBase Database automatically converts unformatted SQL text into formatted SQL text internally.
TO format_target_stmt	Optional. Specifies the target SQL statement to which `format_stmt` will be converted or applied. This allows the outline to also be applicable to other SQL statements. You can use the TO format_target_stmt option to declare these SQL statements. Notice If you want to fix the plan for a statement containing a hint, use `format_target_stmt` to specify the original SQL. When using the `TO format_target_stmt` clause, the original SQL `format_stmt` must match `format_target_stmt` after removing hints and formatting.
format_sql_id	The `format_stmt` obtained after a series of rules are applied to the SQL statement to be bound, and then the `MD5` value is calculated based on `format_stmt` to obtain `format_sql_id`.

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.

Customer Stories

Documentation