Purpose
You can use this statement to create an outline based on SQL_TEXT or SQL_ID. SQL_TEXT is the original statement (with parameters) executed by the user.
Notice
To create an outline, you must log in to the database as the corresponding user.
Syntax
/* Create an outline based on SQL_TEXT. */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* Create an outline based on SQL_ID. */
CREATE OUTLINE outline_name ON sql_id USING HINT hint;
Parameters
| Parameter | Description |
|---|---|
| outline_name | The name of the outline to be created. |
| OR REPLACE | After OR REPLACE is specified, if the outline to be created already exists, the new outline will replace the original one. |
| stmt | It is generally a DML statement with hints and original arguments. |
| TO target_stmt | If TO target_stmt is not specified, when the parameterized SQL statement accepted by the database is the same as the parameterized text of stmt without a hint, the database binds the SQL statement to the hint in stmt to generate an execution plan. If you want to bind plans to statements that have hints, use TO target_stmt to specify the original SQL statement. Notice: When you specify target_stmt, you must ensure that stmt exactly matches target_stmt after hints are removed. |
| sql_id | If the SQL statement of a specific SQL_ID already contains hints, the hints specified when you create an outline will overwrite all hints in the original statement. |
| hint | A hint is specified in the format of /*+ xxx */. For more information about hints, see the "Optimizer hints" topic in OceanBase Database SQL Tuning Guide. |
Examples
Create an outline named
outline1based onSQL_TEXT.obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(tbl1 idx_col2)*/ * FROM tbl1 WHERE col2 = 1;Create an outline named
outline2based onSQL_ID.obclient> CREATE OUTLINE outline2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(tbl1 idx_col2)*/ ;
Considerations
When two outlines have the same SQL_ID, the outline created based on SQL_TEXT overwrites the one created based on SQL_ID, because the former has a higher priority.
In addition, OceanBase Database distinguishes SQL statements by SQL_ID, which is the MD5 hash value of SQL_TEXT. A difference in even one line feed or tab character results in a different SQL_ID. In the actual production system, we recommend that you use the SQL_ID to bind an outline.
