Purpose
This statement is used to create an outline. It can be created in two ways: one is by using SQL_TEXT (the original parameterized statement executed by the user), and the other is by using SQL_ID.
Note
To create an outline, you must log in to the corresponding database and execute the command.
When SQL_ID is the same, an outline created by using the SQL_TEXT method will overwrite an outline created by using the SQL_ID method. The SQL_TEXT method has a higher priority.
Furthermore, OceanBase AI Database uses SQL_ID to distinguish between different SQL statements. SQL_ID is obtained by applying MD5 encryption to SQL_TEXT. Even a single line break or tab character difference in the SQL text results in a different SQL_ID after MD5 hashing. In production systems, it is recommended to bind outlines by using SQL_ID.
Syntax
Create an outline using
SQL_TEXT.CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]Create an outline using
SQL_ID.CREATE OUTLINE outline_name ON sql_id USING HINT hint
Parameters
Parameters |
Description |
|---|---|
| outline_name | Specifies the name of the outline to be created. |
| OR REPLACE | Specify OR REPLACE After that, if the outline to be created already exists, it will replace the existing outline. |
| stmt | It is generally a DML statement with hints and original parameters. |
| TO target_stmt | If not specified, TO target_stmt, which indicates that if the SQL statement parameterized by the database matches the parameterized text of stmt with hints removed, the SQL statement is bound to the execution plan generated by the hints in stmt. To ensure that a statement containing hints always follows a specific plan, you must use the TO target_stmt to specify the original SQL statement. Note When using target_stmt When... is required stmt and target_stmt After removing the hint, they match exactly. |
| sql_id | If the SQL statement corresponding to sql_id already contains hints, the hints specified when creating the outline override all hints in the original statement.fied hints will override all hints in the original statement. |
| hint | The format is /*+ xxx */. |
Examples
Create an outline using
SQL_TEXT.obclient> CREATE OUTLINE otl_idx_c2 ON SELECT/*+ index(t1 idx_c2)*/ * FROM t1 WHERE c2 = 1;Create an outline using
SQL_ID.obclient> CREATE OUTLINE otl_idx_c2 ON 'ED570339F2C856BA96008A29EDF04C74' USING HINT /*+ index(t1 idx_c2)*/ ;