The results of explain plan are stored into EXPLAIN_PLAN_TABLE for evaluation. To use Explain Plan, passed SQL query must be a data manipulation language (DML).
Common DML Statements
- SELECT − retrieve data from the a database
- INSERT − insert data into a table
- UPDATE − updates existing data within a table
SQL Explain Plans cannot be used with DDL and DCL SQL statements.
EXPLAIN PLAN TABLE in database
EXPLAIN PLAN_TABLE in database consists of multiple columns. Few common column names − OPERATOR_NAME, OPERATOR_ID, PARENT_OPERATOR_ID, LEVEL and POSITION, etc.
COLUMN SEARCH value tells the starting position of column engine operators.
ROW SEARCH value tells the starting position of row engine operators.
To create an EXPLAIN PLAN STATEMENT for a SQL query
EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR <SQL DML statement>
To see values in EXPLAIN PLAN TABLE
SELECT Operator_Name, Operator_ID
FROM explain_plan_table
WHERE statement_name = 'statement_name';
To delete a statement in EXPLAIN PLAN TABLE
DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';
No comments:
Post a Comment