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 on 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 | A DML statement with hints and original parameters. |
| TO target_stmt | If you do not specify the TO target_stmt parameter, the parameterized SQL statement accepted by the database is the same as the parameterized statement specified by stmt with hints removed, and the SQL statement is bound to hints in the statement specified by stmt to generate an execution plan. To fix a plan to a statement that contains hints, you need to specify the TO target_stmt parameter to indicate 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 override all hints in the original statement. |
| hint | A hint is specified in the format of /*+ xxx */. For more information about hints, see the "Optimizer Hint" 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.