Purpose
This statement is used to create an outline. You can create an outline in two ways: by using the SQL_TEXT parameter to specify 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.
If 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 higher priority.
In addition, OceanBase Database distinguishes different SQL statements based on the SQL_ID parameter. The SQL_ID parameter is obtained by taking the MD5 hash of the SQL_TEXT parameter. If the SQL texts are the same except for an extra newline or tab, the SQL_ID values obtained by taking the MD5 hash of the SQL texts are different. In a production system, it is recommended to bind an outline by using the SQL_ID parameter.
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 if the specified outline already exists. |
| 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 to the hint in the stmt parameter if the parameterized SQL statement accepted by the database is the same as the stmt parameter after removing the hint and parameterized text. If you want to fix the execution plan for a statement with a hint, you must specify TO target_stmt to indicate the original SQL statement. Note When you use the target_stmt parameter, the stmt and target_stmt parameters must be exactly the same after removing the hints. |
| sql_id | If the SQL statement corresponding to the sql_id parameter already has a hint, the hint specified when creating 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)*/ ;