Purpose
This statement is used to create an outline. An outline can be created in two ways: using SQL_TEXT (the original statement executed by the user with parameters) or using SQL_ID.
Notice
To create an outline, you must execute the statement in the corresponding user context.
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 [OR REPLACE] OUTLINE outline_name ON 'sql_id' USING HINT hint_list
Parameters
| Parameter | Description |
|---|---|
| outline_name | Specifies the name of the outline to be created. |
| OR REPLACE | If specified, the existing outline with the same name will be replaced. |
| stmt | Generally, it is a DML statement with hint and original parameters. |
| TO target_stmt | If not specified, it means that if the SQL statement accepted by the database, after parameterization, is the same as the stmt statement with the hint parameter removed, the SQL statement will be bound to the stmt statement with the hint parameter to generate an execution plan. If you want to fix the execution plan for a statement with a hint, you need to use TO target_stmt to specify the original SQL statement. Notice When using target_stmt, the stmt and target_stmt statements must be exactly the same after removing the hint parameter. |
| sql_id | If the SQL statement corresponding to sql_id already has a hint, the hint specified in the outline will overwrite all existing hint parameters 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
outline1usingSQL_TEXT.obclient> CREATE OUTLINE outline1 ON SELECT/*+ index(employees idx_dept_id)*/ * FROM employees WHERE dept_id = 10;Create an outline named
outline2usingSQL_ID.obclient> -- First, find the SQL_ID of the SQL statement to bind to 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 the outline using the obtained SQL_ID. obclient> CREATE OUTLINE outline2 ON '549D27D6DD54688A9B25FD82D5650B96' USING HINT /*+ index(employees idx_dept_id)*/ ;
Considerations
When the SQL_ID is the same, the outline created using SQL_TEXT will overwrite the outline created using SQL_ID, and the outline created using SQL_TEXT has higher priority.
Additionally, OceanBase Database distinguishes different SQL statements based on SQL_ID, which is obtained by taking the MD5 hash of SQL_TEXT. Even a minor difference, such as an extra newline or tab, in the SQL text will result in a different SQL_ID. In a production system, it is recommended to bind outlines using SQL_ID.