SQL explain plans are used to generate detail explanation of SQL statements. They are used to evaluate execution plan that SAP HANA database follows to execute the SQL statements.
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';
