Purpose
This statement is used to create a fuzzy outline. A fuzzy outline can be created in two ways: one is using the FORMAT_SQL_TEXT (the original statement executed by the user with parameters), and the other is using the FORMAT_SQL_ID.
Limitations and considerations
To create an outline, you need to execute it under the corresponding user.
When
FORMAT_SQL_IDis the same, the outline created usingFORMAT_SQL_TEXTwill overwrite the one created usingFORMAT_SQL_ID. The outline created usingFORMAT_SQL_TEXThas higher priority. Additionally, OceanBase Database distinguishes different SQL statements usingFORMAT_SQL_ID, which is obtained by taking theMD5hash ofFORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines usingFORMAT_SQL_ID.Note
- The matching rules for
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 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 for
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 if the outline to be created already exists, it will replace the existing outline. |
| format_stmt | Specifies the original SQL statement, i.e., the SQL statement or operation to which the outline should 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 should be converted or applied. This allows the outline to also apply 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 the 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 of using outlines, see Plan binding.