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 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.
Required privileges
To execute the CREATE OUTLINE statement, you must have the CREATE privilege. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.
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 parameters. |
| 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 with a specific sql_id value 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 */.
Note
|
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)*/ ;Create an outline based on
SQL_TEXTand specify both a throttling rule and an execution plan in the hint.obclient> CREATE OUTLINE otl_idx_c2 ON SELECT /*+ use_nl(tbl2) max_concurrent(1)*/ * FROM t WHERE c1=?;