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_IDis the same, the outline created by usingFORMAT_SQL_TEXTwill overwrite the outline created by usingFORMAT_SQL_ID. The outline created by usingFORMAT_SQL_TEXThas a higher priority. Additionally, OceanBase Database distinguishes different SQL statements by usingFORMAT_SQL_ID, which is generated by taking theMD5hash ofFORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines by usingFORMAT_SQL_ID.Note
- The matching rules of
FORMAT_SQL_TEXTignore changes in parameters, case differences, and non-syntactic symbols such as spaces and line breaks. For anINexpression, normalization is performed, and only the text content after these processing steps is compared. FORMAT_SQL_IDis generated by first formattingFORMAT_SQL_TEXT(removing extra spaces and line breaks, and converting all characters to uppercase or lowercase), and then applying theMD5hash encryption to the formatted result.
- The matching rules of
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.
NoteOceanBase 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
|
| 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.