Purpose
This statement is used to create an outline. You can create an outline in two ways: by using the SQL_TEXT parameter (the original statement executed by the user with parameters) or by using the SQL_ID parameter.
Note
To create an outline, you must be in the corresponding database.
If 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 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. If the SQL text is the same but has an extra newline or tab character, the MD5 hash will be different. In a production system, it is recommended to bind an outline 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 if the outline to be created already exists. |
| stmt | Generally, it is 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 stmt if the parameterized SQL statement accepted by the database is the same as the statement in stmt with the hint removed. 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. Notice When you use target_stmt, the statement in stmt and the statement in target_stmt must be the same after the hints are removed. |
| sql_id | If the SQL statement corresponding to the sql_id parameter 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)*/ ;