Purpose
This statement is used to create a fuzzy outline. You can create a fuzzy outline in two ways: one is by using the FORMAT_SQL_TEXT parameter (which specifies the original statement executed by the user with parameters), and the other is by using the FORMAT_SQL_ID parameter.
Limitations and considerations
To create an outline, you must execute the statement in the corresponding user account.
If the
FORMAT_SQL_IDis the same, the outline created by using theFORMAT_SQL_TEXTparameter will overwrite the outline created by using theFORMAT_SQL_IDparameter. The outline created by using theFORMAT_SQL_TEXTparameter has higher priority. In addition, OceanBase Database distinguishes different SQL statements by using theFORMAT_SQL_ID, which is generated by taking theMD5hash of theFORMAT_SQL_TEXT. In a production system, we recommend that you bind outlines by using theFORMAT_SQL_IDparameter.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 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 by using the FORMAT_SQL_TEXT parameter. */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]
/* Create an outline by using the FORMAT_SQL_ID parameter. */
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, the existing outline will be replaced if the specified outline already exists. |
| format_stmt | The original SQL statement.
NoteOceanBase Database automatically converts unformatted SQL statements to formatted SQL statements. |
| TO format_target_stmt | Optional. Specifies the target SQL statement to which the format_stmt will be converted or applied. This allows the outline to 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_sql_id is generated by rewriting the SQL statement according to a series of rules to obtain the format_stmt, and then calculating the MD5 value based on the format_stmt. |
Examples
Create an outline by using the
FORMAT_SQL_TEXTparameter.CREATE FORMAT OUTLINE my_outline ON 'SELECT * FROM employees WHERE department_id = ?';Create an outline by using the
FORMAT_SQL_IDparameter.CREATE FORMAT OUTLINE my_sql_id_outline ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(tbl1 idx_col2)*/;
References
For more information about using outlines, see Plan binding.