Purpose
This statement is used to create an outline. You can create an outline in two ways: by using the SQL_TEXT parameter, which specifies the original statement with parameters executed by the user, or by using the SQL_ID parameter.
Note
To create an outline, you must be in the corresponding database.
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, which is obtained by encrypting the SQL_TEXT with MD5. If the SQL texts are the same except for an extra line break or tab, the SQL_ID will be different. In a production system, we recommend that you bind an outline to an SQL statement by using the SQL_ID.
Syntax
Create an outline by using the
SQL_TEXTparameter.CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]Create an outline by using the
SQL_IDparameter.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 when you create a new one. |
| stmt | A DML statement with hints and parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the database will bind the SQL statement with the same parameters as the stmt statement to the hint in the stmt statement. If you want to fix the execution plan of a statement with hints, you must specify TO target_stmt to indicate the original SQL statement. Notice When you use target_stmt, the stmt statement and the target_stmt statement must be exactly the same after the hints are removed. |
| sql_id | If the SQL statement corresponding to the sql_id parameter has hints, the hints specified in the outline will overwrite all hints in the original statement. |
| hint | The format is /*+ xxx */. |
Examples
Create an outline by using the
SQL_TEXTparameter.obclient> CREATE OUTLINE otl_idx_c2 ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;Create an outline by using the
SQL_IDparameter.obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(t1 idx_c2)*/ ;