Purpose
You can use this statement to create a fuzzy outline based on SQL_TEXT or SQL_ID. SQL_TEXT is the original statement (with parameters) executed by the user.
Notice
To create an outline, you must log on to the database as the corresponding user.
Considerations
When two outlines have the same SQL_ID, the outline created based on SQL_TEXT overwrites the one created based on SQL_ID, because the former has a higher priority.
In addition, OceanBase Database distinguishes SQL statements by SQL_ID, which is the MD5 hash value of SQL_TEXT. A difference in even one line feed or tab character results in a different SQL_ID. In the actual production system, we recommend that you use the SQL_ID to bind an outline.
Syntax
/* Create an outline based on SQL_TEXT. */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_stmt [ TO format_target_stmt ]
/* Create an outline based on SQL_ID. */
CREATE [OR REPLACE] FORMAT OUTLINE outline_name ON format_sql_id USING HINT hint;
Parameters
| Parameter | Description |
|---|---|
| outline_name | The name of the fuzzy outline to be created. |
| OR REPLACE | After OR REPLACE is specified, if the outline to be created already exists, the new outline will replace the original one. |
| format_stmt | The statement obtained after the original SQL statement is rewritten based on a series of rules. Usually, you can obtain format_stmt by using an expression or a DBMS package. |
| TO format_target_stmt | You can obtain format_target_stmt in the same way as format_stmt. However, if you want to fix the execution plan for a statement with a hint, use format_target_stmt to specify the original SQL statement. When you use the TO format_target_stmt clause, format_stmt of the original SQL statement must exactly match format_target_stmt without the hint. |
| format_sql_id | The MD5 value of format_stmt obtained after the bound SQL statement is rewritten based on a series of rules. |
Examples
Create a table named employees and insert data.
-- Create a table
CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(50),
department_id INT
);
-- Insert data
INSERT INTO employees (employee_id, employee_name, department_id) VALUES
(1, 'John', 10),
(2, 'Jane', 20),
(3, 'Doe', 10),
(4, 'Alice', 20),
(5, 'Bob', 30);
Create an outline based on SQL_TEXT
-- Create an outline
CREATE FORMAT OUTLINE my_outline ON 'SELECT * FROM employees WHERE department_id = ?';
-- Use the outline
SELECT /*+ my_outline */ * FROM employees WHERE department_id = 20;
In this example, CREATE FORMAT OUTLINE creates an outline based on SQL_TEXT, and then the outline is used by adding the /*+ my_outline */ comment to the SQL statement.
The return result is as follows:
+-------------+---------------+---------------+
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_ID |
+-------------+---------------+---------------+
| 2 | Jane | 20 |
| 4 | Alice | 20 |
+-------------+---------------+---------------+
2 rows in set
Create an outline based on SQL_ID
-- Obtain SQL_ID
SELECT SQL_ID FROM v$sql WHERE sql_text = 'SELECT * FROM employees WHERE department_id = ?';
-- Create an outline
CREATE FORMAT OUTLINE my_sql_id_outline ON SQL_ID 'b5mgha4x5gs1j';
-- Use the outline
SELECT /*+ my_sql_id_outline */ * FROM employees WHERE department_id = 10;
In this example, SQL_ID of the SQL statement is first obtained from the v$sql view, then CREATE FORMAT OUTLINE creates an outline based on SQL_ID, and finally the outline is used by adding the /*+ mys_sql_id_outline */ comment to the actual SQL statement.
The return result is as follows:
+-------------+---------------+---------------+
| EMPLOYEE_ID | EMPLOYEE_NAME | DEPARTMENT_ID |
+-------------+---------------+---------------+
| 1 | John | 10 |
| 3 | Doe | 10 |
+-------------+---------------+---------------+
2 rows in set