Purpose
This statement is used to create an outline. You can create an outline in two ways: one is by using SQL_TEXT (the original statement executed by the user with parameters), and the other is by using SQL_ID.
Note
To create an outline, you must execute the statement in the corresponding database.
When SQL_ID is the same, the outline created by using SQL_TEXT will overwrite the outline created by using SQL_ID. The outline created by using SQL_TEXT has a higher priority.
In addition, OceanBase Database distinguishes different SQL statements by using SQL_ID, which is obtained by taking the MD5 hash of SQL_TEXT. Even if the SQL text is the same, different line breaks or tab characters will result in different SQL_ID values. In a production system, we recommend that you bind an outline by using SQL_ID.
Privilege requirements
To execute the CREATE OUTLINE statement, the current user must have the CREATE privilege. For more information about the privileges of OceanBase Database, see Privilege types in MySQL mode.
Syntax
Create an outline by using
SQL_TEXT.CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ];Create an outline by using
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 | If you specify OR REPLACE, the existing outline will be replaced if the specified outline already exists. |
| stmt | Generally, this parameter specifies a DML statement with hints and original parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the database will bind the SQL statement to the hint in stmt if the SQL statement parameterized by the database is the same as the statement in stmt without hints. If you want to fix the execution plan for a statement with hints, you must specify TO target_stmt to indicate the original SQL statement. Notice When you use target_stmt, the statement in stmt must be exactly the same as the statement in target_stmt without hints. |
| sql_id | If the SQL statement corresponding to sql_id already has hints, the hints specified in the outline will overwrite all hints in the original statement. |
| hint | The format is /*+ xxx */.
Note
|
Examples
Create an outline using
SQL_TEXT.obclient> CREATE OUTLINE otl_idx_c2 ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;Create an outline using
SQL_ID.obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(t1 idx_c2)*/ ;Create an outline using
SQL_TEXTand specify both throttling and execution plans in the HINT:obclient> CREATE OUTLINE otl_idx_c2 ON SELECT /*+ use_nl(tbl2) max_concurrent(1)*/ * FROM t WHERE c1=?;