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 (the original statement executed by the user with parameters), and the other is by using the SQL_ID parameter.
Notice
To create an outline, you must execute the statement in 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 | Generally, 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 to the stmt with the hint parameter if the SQL statement after parameterization is the same as the stmt with the hint parameter removed. Notice When you use target_stmt, the stmt and target_stmt must be exactly the same after the hint parameter is removed. |
| sql_id | If the SQL statement corresponding to the sql_id parameter already has hint, the hint specified in the outline creation statement will overwrite all 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". |
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 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. The SQL_ID is obtained by taking the MD5 hash of the SQL_TEXT. Therefore, even if the same SQL statement has an extra newline or tab character, the SQL_ID will be different. In a production system, we recommend that you bind an outline by using the SQL_ID parameter.