SAP HANA - SQL Explain Plans

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';