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 executed by the user with parameters), 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 byFORMAT_SQL_TEXTwill overwrite the outline created byFORMAT_SQL_ID. The outline created byFORMAT_SQL_TEXThas a higher priority. Additionally, OceanBase Database distinguishes different SQL statements based onFORMAT_SQL_ID, which is generated by taking theMD5hash ofFORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines usingFORMAT_SQL_ID.Note
- The matching rules of
FORMAT_SQL_TEXTignore differences in parameter content, case, and non-syntactic symbols such as spaces and line breaks, and only compare the processed text content. FORMAT_SQL_IDis generated by formattingFORMAT_SQL_TEXT(removing extra spaces and line breaks, and converting all characters to uppercase or lowercase), then applying theMD5hash encryption to the formatted result.
- The matching rules of
Syntax
/* Create an outline using FORMAT_SQL_TEXT */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]
/* Create an outline using FORMAT_SQL_ID */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_sql_id USING HINT hint;
Parameters
| Parameter | Description |
|---|---|
| outline_name | Specifies the name of the fuzzy outline to be created. |
| OR REPLACE | If specified, and the outline to be created already exists, the existing outline will be replaced. |
| format_stmt | Specifies the original SQL statement, i.e., the SQL statement or operation to which the outline will be applied.
NoteOceanBase Database automatically converts non-formatted 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, i.e., making the outline 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 corresponding to the SQL statement to be bound, which is generated after a series of rule-based rewriting, and then the MD5 value is calculated based on format_stmt to obtain format_sql_id. |
Examples
Create an outline using
FORMAT_SQL_TEXT.CREATE FORMAT OUTLINE my_outline ON SELECT * FROM employees WHERE department_id = ?;Create an outline using
FORMAT_SQL_ID.CREATE FORMAT OUTLINE my_sql_id_outline ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(t1 idx_c2)*/;
References
For more examples on using outlines, see Plan binding.