Purpose
This statement is used to create an outline. An outline can be created in two ways: one is by using SQL_TEXT (the original statement with parameters executed by the user), and the other is by using SQL_ID.
Notice
To create an outline, you must execute the statement under the corresponding user.
Syntax
/* Create an outline using SQL_TEXT. */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* Create an outline 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 one with the same name already exists. |
| stmt | Generally, it is a DML statement with hint and original parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the database will bind the SQL statement in stmt to the original SQL statement after parameterization, provided that the parameterized SQL statement in the database is the same as the one in stmt without the hint parameter. If you want to fix the execution plan for a statement with hint, you must specify TO target_stmt to indicate the original SQL statement. Notice When you use target_stmt, the stmt and target_stmt must be exactly the same after removing the hint. |
| sql_id | If the SQL statement corresponding to sql_id already has hint, the hint specified when creating the outline will overwrite all the hint in the original statement. |
| hint | The format is /*+ xxx */. For more information about the syntax of hints, see the Optimizer Hint section in the "OceanBase Database SQL Tuning Guide".
Note
|
Examples
Create an outline named
outline1usingSQL_TEXT.obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(tbl1 idx_col2)*/ * FROM tbl1 WHERE col2 = 1;Create an outline named
outline2usingSQL_ID.obclient> CREATE OUTLINE outline2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(tbl1 idx_col2)*/ ;Create an outline named
outline1usingSQL_TEXTand specify both throttling and an execution plan in theHINT:obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(tbl1 idx_col2) max_concurrent(1)*/ * FROM tbl1 WHERE col2 = 1;
Considerations
When the SQL_ID is the same, the outline created using the SQL_TEXT method will override the outline created using the SQL_ID method. The outline created using the SQL_TEXT method has a higher priority.
Additionally, OceanBase Database distinguishes different SQL statements based on the SQL_ID, which is obtained by taking the MD5 hash of the SQL_TEXT. Even a minor difference, such as an extra newline or tab, in the SQL text will result in a different SQL_ID. In a production system, it is recommended to bind outlines using the SQL_ID method.