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).
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 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.
EXPLAIN PLAN SET STATEMENT_NAME = ‘statement_name’ FOR <SQL DML statement>
SELECT Operator_Name, Operator_ID FROM explain_plan_table WHERE statement_name = 'statement_name';
DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';