Purpose
This statement is used to create an outline. An outline can be created in two ways: one is by using SQL_TEXT (the original statement executed by the user with parameters), and the other is by using SQL_ID.
Notice
To create an outline, you must execute the statement in the corresponding user account.
Syntax
/* Create an outline by using SQL_TEXT */
CREATE [OR REPLACE] OUTLINE outline_name ON stmt [ TO target_stmt ]
/* Create an outline by using SQL_ID */
CREATE [OR REPLACE] OUTLINE outline_name ON 'sql_id' USING HINT hint_list
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 | A DML statement with hint and original parameters. |
| TO target_stmt | If you do not specify TO target_stmt, the database will parameterize the SQL statement accepted by the database, remove the hint parameterized text, and compare it with the stmt statement. If they are the same, the SQL statement will be bound to the stmt statement with the hint to generate an execution plan. If you want to fix the plan for a statement with hint, you need to specify TO target_stmt to indicate the original SQL. Notice When using target_stmt, the stmt and target_stmt statements must be exactly the same after removing the hint parameterized text. |
| sql_id | If the SQL statement corresponding to sql_id already has a hint, the hint specified in the outline 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 a test table and insert data.
obclient> CREATE TABLE employees ( emp_id INT, dept_id INT, salary INT, emp_name VARCHAR(50) ); obclient> INSERT INTO employees VALUES (1001, 10, 10000, 'John'), (1002, 10, 12000, 'Mike'), (1003, 20, 15000, 'Sarah'); obclient> CREATE INDEX idx_dept_id ON employees(dept_id);Create an outline named
outline1by usingSQL_TEXT.obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(employees idx_dept_id)*/ * FROM employees WHERE dept_id = 10;Create an outline named
outline2by usingSQL_ID.obclient> -- First, view the SQL_ID of the SQL statement to be bound with the outline. obclient> SELECT sql_id, trace_id, client_ip, user_name, query_sql FROM V$OB_SQL_AUDIT WHERE query_sql LIKE '%employees%'; obclient> -- Create an outline based on the SQL_ID obtained. obclient> CREATE OUTLINE outline2 ON '549D27D6DD54688A9B25FD82D5650B96' USING HINT /*+ index(employees idx_dept_id)*/ ;
Considerations
When the SQL_ID is the same, the outline created by using SQL_TEXT will overwrite the outline created by using SQL_ID. The outline created by using SQL_TEXT has a higher priority.
In addition, OceanBase Database distinguishes different SQL statements by using SQL_ID, which is obtained by taking the MD5 hash of SQL_TEXT. Even if the SQL text is the same, the SQL_ID will be different if there is an extra newline or tab character. In a production system, it is recommended to bind outlines by using SQL_ID.