Purpose
This statement is used to create an outline. An outline can be created in two ways: one is by using the SQL_TEXT parameter (which contains the original statement with parameters executed by the user), and the other is by using the SQL_ID parameter.
Notice
To create an outline, you must execute the statement under the corresponding user.
Syntax
/* Create an outline by using the SQL_TEXT parameter */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* Create an outline by using the SQL_ID parameter */
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 with the same name will be replaced. |
| stmt | A DML statement that contains the hint and original parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the database will bind the SQL statement to the stmt statement and generate an execution plan based on the hint parameter. Notice When you use target_stmt, the stmt statement and target_stmt statement must be exactly the same after the hint parameter is removed. |
| sql_id | If the SQL statement corresponding to the sql_id parameter already contains a hint, the hint specified in the outline creation will overwrite all existing hint parameters 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. |
Examples
Create an outline named
outline1by using theSQL_TEXTparameter.obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(tbl1 idx_col2)*/ * FROM tbl1 WHERE col2 = 1;Create an outline named
outline2by using theSQL_IDparameter.obclient> CREATE OUTLINE outline2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(tbl1 idx_col2)*/ ;
Considerations
When the SQL_ID parameter is the same, the outline created by using the SQL_TEXT parameter will overwrite the outline created by using the SQL_ID parameter. The outline created by using the SQL_TEXT parameter has a higher priority.
In addition, OceanBase Database distinguishes different SQL statements by using the SQL_ID parameter. The SQL_ID parameter is obtained by encrypting the SQL_TEXT parameter with the MD5 algorithm. If the SQL statements are the same except for an extra line break or tab, the SQL_ID values obtained by using the MD5 algorithm will be different. In actual production systems, we recommend that you bind outlines by using the SQL_ID parameter.