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 parameterized statement executed by the user), and the other is by using FORMAT_SQL_ID.
Limitations and considerations
Outlines must be created under the corresponding user.
When
FORMAT_SQL_IDis the same, an outline created by using theFORMAT_SQL_TEXTmethod will overwrite an outline created by using theFORMAT_SQL_IDmethod. TheFORMAT_SQL_TEXTmethod has a higher priority. Furthermore, OceanBase AI Database usesFORMAT_SQL_IDto distinguish different SQL statements, andFORMAT_SQL_IDis obtained by applyingMD5encryption toFORMAT_SQL_TEXT. In a production system, it is recommended to bind outlines usingFORMAT_SQL_ID.Note
- The matching rules of
FORMAT_SQL_TEXTignore the parameter content, case, and non-syntactic symbols such as spaces and line breaks. It only compares the text content after these preprocessing steps. FORMAT_SQL_IDis generated by formattingFORMAT_SQL_TEXT(removing extra spaces and line breaks, and converting all characters to uppercase or lowercase uniformly), and then applyingMD5hash encryption to the formatted result.
- The matching rules of
Syntax
/*Use FORMAT_SQL_TEXT to create an outline*/
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]
/*Use FORMAT_SQL_ID to create an outline.*/
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_sql_id USING HINT hint;
Parameters
Parameter |
Description |
|---|---|
| outline_name | Specify the name of the fuzzy outline to be created. |
| OR REPLACE | If specified, OR REPLACE, and the Outline to be created already exists, the existing Outline will be replaced. |
| format_stmt | Specifies the original SQL text, which is the SQL statement or operation for which you want to apply an outline.
NoteOceanBase AI Database automatically processes unformatted SQL text into formatted SQL text. |
| TO format_target_stmt | Optional. Optional. Specifies the target SQL statements to which format_stmt is converted or applied. That is, to make 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 SQL statement to be bound, which has been rewritten according to a series of rules format_stmt, and then based on format_stmt Calculate MD5 Obtained from the value of format_sql_id. |
Examples
Use
FORMAT_SQL_TEXTto create an outline.CREATE FORMAT OUTLINE my_outline ON SELECT * FROM employees WHERE department_id = ?;Use
FORMAT_SQL_IDto create an outline.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.