Purpose
You can use this statement to create an outline based on SQL_TEXT or SQL_ID. SQL_TEXT is the original statement that is executed by the user and that has arguments.
Note
To create an outline, you must go to the corresponding database.
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 using 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 SQL_ID to bind an outline.
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 override all hints in the original statement. |
| hint | A hint is specified in the format of /*+ xxx */. |
Examples
Create an outline based on
SQL_TEXT.obclient> CREATE OUTLINE otl_idx_c2 ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;Create an outline based on
SQL_ID.obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(t1 idx_c2)*/ ;