SAP HANA Reference
EXPLAIN PLAN

EXPLAIN PLAN

 EXPLAIN PLAN [SET STATEMENT_NAME = <statement_name>] FOR \ref sql_subquery

Syntax Elements

 <statement_name> ::= string literal used to identify the name of a specific execution plan in the output table for a given SQL statement.  

It is set to NULL if the SET STATEMENT_NAME is not specified.

Description

The EXPLAIN PLAN statement is used to evaluate the execution plan that the SAP HANA database follows to execute an SQL statement. The result of the evaluation is stored into the EXPLAIN_PLAN_TABLE view for later user examination.

The SQL statement must be data manipulation statement, thus a schema definition language statement cannot be used with the EXPLAIN PLAN command.

You can obtain SQL plan from EXPLAIN_PLAN_TABLE view. The view is shared by all users. Here is an example of reading an SQL plan from the view.

 SELECT * FROM EXPLAIN_PLAN_TABLE;

Columns in EXPLAIN_PLAN_TABLE view:

Table 1: Column name and description

Column NameDescription
STATEMENT_NAMEThe string specified as STATEMENT_NAME on executing the EXPLAIN PLAN command. This is used to distinguish plans from each other when there are multiple plans in the EXPLAIN_PLAN_TABLE view.
OPERATOR_NAMEName of an operator. Details are described in the following section.
OPERATOR_DETAILSDetails of an operator. Predicates and expressions used by the operator are shown here.
SCHEMA_NAMEName of the schema of the accessed table.
TABLE_NAMEName of the accessed table.
TABLE_TYPEType of the accessed table. One of the following options: COLUMN TABLE, ROW TABLE, MONITORING VIEW, JOIN VIEW, OLAP VIEW, CALCULATION VIEW and HIERARCHY VIEW.
TABLE_SIZEEstimated number of rows in the accessed table
OUTPUT_SIZE Estimated number of rows produced by an operator
SUBTREE_COSTEstimated cost of executing the subtree starting from an operator. This value is only for relative comparison.
OPERATOR_IDID of an operator unique in a plan. IDs are integers starting from 1.
PARENT_OPERATOR_IDOPERATOR_ID of the parent of an operator. The shape of an SQL plan is a tree and the topology of the tree can be reconstructed using OPERATOR_ID and PARENT_OPERATOR_ID. PARENT_OPERATOR_ID of the root operator is shown as NULL.
LEVELLevel from the root operator. Level of the root operator is 1, level of a child of the root operator is 2 and so on. This can be utilized for output indentation.
POSITIONPosition in the parent operator. Position of the first child is 1, position of the second child is 2 and so on.
HOSTThe hostname where an operator was executed
PORTThe TCP/IP port used to connect to the host
TIMESTAMPDate and time when the EXPLAIN PLAN command was executed.
CONNECTION_IDID of the connection where the EXPLAIN PLAN command was executed.
EXECUTION_ENGINEType of the execution engine where an operator is executed: COLUMN or ROW

OPERATOR_NAME column in EXPLAIN_PLAN_TABLE view: Table 2. List of column engine operators shown in the OPERATOR_NAME column.

Operator NameDescription
COLUMN SEARCHStarting position of column engine operators. OPERATOR_DETAILS lists projected columns.
LIMITOperator for limiting the number of output rows
ORDER BYOperator for sorting output rows
HAVINGOperator for filtering with predicates on top of grouping and aggregation
GROUP BYOperator for grouping and aggregation
DISTINCTOperator for duplicate elimination
FILTEROperator for filtering with predicates
JOINOperator for joining input relations
COLUMN TABLEInformation about accessed column table
MULTIPROVIDEROperator for producing union-all of multiple results having the same grouping and aggregation

Table 3. List of row engine operators shown in the OPERATOR_NAME column.

Operator NameDescription
ROW SEARCHStarting position of row engine operators. OPERATOR_DETAILS lists projected columns.
LIMITOperator for limiting number of output rows
ORDER BYOperator for sorting output rows
HAVINGOperator for filtering with predicates on top of grouping and aggregation
GROUP BYOperator for grouping and aggregation
MERGE AGGREGATIONOperator for merging the results of multiple parallel grouping and aggregations
DISTINCTOperator for duplicate elimination
FILTEROperator for filtering with predicates
UNION ALLOperator for producing union-all of input relations
MATERIALIZED UNION ALLOperator for producing union-all of input relations with intermediate result materialization
BTREE INDEX JOINOperator for joining input relations through B-tree index searches. Join type suffix can be added. For example, B-tree index join for left outer join is shown as BTREE INDEX JOIN (LEFT OUTER). Join without join type suffix means inner join.
CPBTREE INDEX JOINOperator for joining input relations through CPB-tree index searches. Join type suffix can be added.
HASH JOINOperator for joining input relations through probing hash table built on the fly. Join type suffix can be added.
NESTED LOOP JOINOperator for joining input relations through nested looping. Join type suffix can be added.
MIXED INVERTED INDEX JOINOperator for joining an input relation of row store format with a column table without format conversion using an inverted index of the column table. Join type suffix can be added.
BTREE INDEX SEARCHTable access through B-tree index search
CPBTREE INDEX SEARCHTable access through CPB-tree index search
TABLE SCANTable access through scanning
AGGR TABLEOperator for aggregating base table directly
MONITOR SEARCHMonitoring view access through search
MONITOR SCANMonitoring view access through scanning

COLUMN SEARCH is a mark for the starting position of column engine operators and ROW SEARCH is a mark for the starting position of row engine operators. In the example below, the intermediate result produced by a COLUMN SEARCH (ID 10) is consumed by a ROW SEARCH (ID 7), and the intermediate result produced by the ROW SEARCH (ID 7) is consumed by another COLUMN SEARCH (ID 1). The operators below the lowest COLUMN SEARCH (ID 10) explain how the COLUMN SEARCH (ID 10) is executed. The operators between the ROW SEARCH (ID 7) and the COLUMN SEARCH (ID 10) explain how the ROW SEARCH (ID 7) processes the intermediate result produced by the COLUMN SEARCH (ID 10). The operators between the top COLUMN SEARCH (ID 1) and the ROW SEARCH (ID 7) explain how the top COLUMN SEARCH (ID 1) processes the intermediate result produced by the ROW SEARCH (ID 7). Table 4. Operators

OPERATOR_NAMEOPERATOR_IDPARENT_OPERATOR_IDLEVELPOSITION
COLUMN SEARCH1NULL11
  LIMIT2121
    ORDER BY3231
      GROUP BY4341
        JOIN5451
          COLUMN TABLE6561
            <font color="BLUE">ROW SEARCH</font><font color="BLUE">7</font><font color="BLUE">5</font><font color="BLUE">6</font><font color="BLUE">2</font>
              BTREE INDEX JOIN8771
                BTREE INDEX JOIN9881
                  <font color="RED">COLUMN SEARCH</font><font color="RED">10</font><font color="RED">9</font><font color="RED">9</font><font color="RED">1</font>
                    FILTER1110101
                      COLUMN TABLE1211111

Example of SQL plan explanation Here is an example of SQL plan explanation of a query. The query is from TPC-H Benchmark. In the example, all tables are located on row store.

DELETE FROM explain_plan_table WHERE statement_name = 'TPC-H Q10';

EXPLAIN PLAN SET STATEMENT_NAME = 'TPC-H Q10' FOR
SELECT TOP 20
    c_custkey,
    c_name,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
FROM
    customer,
    orders,
    lineitem,
    nation
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate >= '1993-10-01'
    AND o_orderdate < ADD_MONTHS('1993-10-01',3)
    AND l_returnflag = 'R'
    AND c_nationkey = n_nationkey
GROUP BY
c_custkey,
c_name,
c_acctbal,
c_phone,
n_name,
c_address,
c_comment
ORDER BY
    revenue DESC;

SELECT operator_name, operator_details, table_name
FROM explain_plan_table
WHERE statement_name = 'TPC-H Q10';

The following is the plan explanation of the above query.

OPERATOR_NAMEOPERATOR_DETAILSTABLE_NAME
ROW SEARCHCUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)), CUSTOMER.C_ACCTBAL, NATION.N_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_PHONE, CUSTOMER.C_ COMMENTNone
  LIMITNUM RECORDS: 20 
    ORDER BYSUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) DESCNone
      MERGE AGGREGATIONNUM PARTITIONS: 4None
        GROUP BYGROUPING: NATION.N_NAME, R_CUSTOMER.C_CUSTKEY, AGGREGATION: SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT))None
          CPBTREE INDEX JOININDEX NAME: _SYS_TREE_RS_279_#0_#P0,
INDEX CONDITION: ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY,
INDEX FILTER: 'R' = LINEITEM.L_RETURNFLAG
LINEITEM
            BTREE INDEX JOININDEX NAME: _SYS_TREE_RS_285_#0_#P0,
INDEX CONDITION: CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY
NATION
              BTREE INDEX JOININDEX NAME: _SYS_TREE_RS_283_#0_#P0,
INDEX CONDITION: ORDERS.O_CUSTKEY = CUSTOMER.C_CUSTKEY
CUSTOMER
                TABLE SCANFILTER CONDITION: ORDERS.O_ORDERDATE < '1994-01-01' AND ORDERS.O_ORDERDATE >= '1993-10-01'

ORDERS

This means that:
1. TABLE SCAN will be executed on ORDERS with the FILTER CONDITION.
2. BTREE INDEX JOIN will be executed with the B-tree index of CUSTOMER and the result of the below TABLE SCAN.
3. BTREE INDEX JOIN will be executed with the B-tree index of NATION and the result of the below BTREE INDEX JOIN.
4. CPBTREE INDEX JOIN will be executed with the CPB-tree index of LINEITEM and the result of the below BTREE INDEX JOIN.
5. GROUP BY will be executed with the result of the below CPBTREE INDEX JOIN, with 4 threads.
6. MERGE AGGREGATION will be executed with the result of the GROUP BY below.