EXPLAIN PLAN EXPLAIN PLAN

Example

DELETE FROM table_a WHERE a = 1;

EXPLAIN PLAN EXPLAIN PLAN

EXPLAIN PLAN [SET STATEMENT_NAME = <statement_name>] FOR SELECT <subquery>

Syntax

<statement_name> ::= string literal used to identify the name of a specific executi on 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 Col umn Na me Des cri pti on

STATEMENT_NAME

The s tri ng s peci fi ed a s STATEMENT_NAME on executi ng the EXPLAIN PLAN comma nd. Thi s i s us ed to di s ti ngui s h pl a ns from ea ch other when there a re mul ti pl e pl a ns i n the EXPLAIN_PLAN_TABLE vi ew.

OPERATOR_NAME Na me of a n opera tor. Deta i l s a re des cri bed i n the fol l owi ng s ecti on.

OPERATOR_DETAILS Deta i l s of a n opera tor. Predi ca tes a nd expres s i ons us ed by the opera tor a re s hown here.

SCHEMA_NAME Na me of the s chema of the a cces s ed ta bl e.

TABLE_NAME Na me of the a cces s ed ta bl e.

TABLE_TYPE Type of the a cces s ed ta bl e. One of the fol l owi ng opti ons : COLUMN TABLE, ROW TABLE, MONITORING VIEW, JOIN VIEW, OLAP VIEW, CALCULATION VIEW a nd HIERARCHY VIEW.

TABLE_SIZE Es ti ma ted number of rows i n the a cces s ed ta bl e OUTPUT_SIZE Es ti ma ted number of rows produced by a n opera tor

SUBTREE_COST Es ti ma ted cos t of executi ng the s ubtree s ta rti ng from a n opera tor. Thi s va l ue i s onl y for rel a ti ve compa ri s on.

OPERATOR_ID ID of a n opera tor uni que i n a pl a n. IDs a re i ntegers s ta rti ng from 1.

PARENT_OPERATOR_ID

OPERATOR_ID of the pa rent of a n opera tor. The s ha pe of a n SQL pl a n i s a tree a nd the topol ogy of the tree ca n be recons tructed us i ng OPERATOR_ID a nd

PARENT_OPERATOR_ID. PARENT_OPERATOR_ID of the root opera tor i s s hown a s NULL.

LEVEL Level from the root opera tor. Level of the root opera tor i s 1, l evel of a chi l d of the root opera tor i s 2 a nd s o on. Thi s ca n be uti l i zed for output i ndenta ti on.

POSITION Pos i ti on i n the pa rent opera tor. Pos i ti on of the fi rs t chi l d i s 1, pos i ti on of the s econd chi l d i s 2 a nd s o on.

HOST The hos tna me where a n opera tor wa s executed PORT The TCP/IP port us ed to connect to the hos t

TIMESTAMP Da te a nd ti me when the EXPLAIN PLAN comma nd wa s executed.

CONNECTION_ID ID of the connecti on where the EXPLAIN PLAN comma nd wa s executed.

EXECUTION_ENGINE Type of the executi on engi ne where a n opera tor i s 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.

Opera tor Na me Des cri pti on COLUMN

SEARCH Sta rti ng pos i ti on of col umn engi ne opera tors . OPERATOR_DETAILS l i s ts projected col umns . LIMIT Opera tor for l i mi ti ng the number of output rows

ORDER BY Opera tor for s orti ng output rows

HAVING Opera tor for fi l teri ng wi th predi ca tes on top of groupi ng a nd a ggrega ti on GROUP BY Opera tor for groupi ng a nd a ggrega ti on

DISTINCT Opera tor for dupl i ca te el i mi na ti on FILTER Opera tor for fi l teri ng wi th predi ca tes JOIN Opera tor for joi ni ng i nput rel a ti ons COLUMN TABLE Informa ti on a bout a cces s ed col umn ta bl e

MULTIPROVIDEROpera tor for produci ng uni on-a l l of mul ti pl e res ul ts ha vi ng the s a me groupi ng a nd a ggrega ti on

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

Opera tor

Na me Des cri pti on

ROW SEARCH Sta rti ng pos i ti on of row engi ne opera tors . OPERATOR_DETAILS l i s ts projected col umns . LIMIT Opera tor for l i mi ti ng number of output rows

ORDER BY Opera tor for s orti ng output rows

HAVING Opera tor for fi l teri ng wi th predi ca tes on top of groupi ng a nd a ggrega ti on GROUP BY Opera tor for groupi ng a nd a ggrega ti on

MERGE

AGGREGATIONOpera tor for mergi ng the res ul ts of mul ti pl e pa ra l l el groupi ng a nd a ggrega ti ons DISTINCT Opera tor for dupl i ca te el i mi na ti on

FILTER Opera tor for fi l teri ng wi th predi ca tes

UNION ALL Opera tor for produci ng uni on-a l l of i nput rel a ti ons

HASH JOIN Opera tor for joi ni ng i nput rel a ti ons through probi ng ha s h ta bl e bui l t on the fl y. Joi n type s uffi x

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_NAME OPERATOR_ID PARENT_OPERATOR_ID LEVEL POSITION

COLUMN SEARCH 1 NULL 1 1

LIMIT 2 1 2 1

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 expl a i n_pl a n_ta bl e WHERE s ta tement_na me = 'TPC-H Q10';

EXPLAIN PLAN SET STATEMENT_NAME = 'TPC-H Q10' FOR SELECT TOP 20

AND o_orderda te < ADD_MONTHS('1993-10-01',3) AND l _returnfl a g = 'R'

AND c_na ti onkey = n_na ti onkey

SELECT opera tor_na me, opera tor_deta i l s , ta bl e_na me FROM expl a i n_pl a n_ta bl e

WHERE s ta tement_na me = 'TPC-H Q10';

The following is the plan explanation of the above query.

OPERATOR_NAME OPERATOR_DETAILS TABLE_NAME

ROW SEARCH

CUSTOMER.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_

COMMENT

None

LIMIT NUM RECORDS: 20

ORDER BY SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) DESC None

MERGE AGGREGATION NUM PARTITIONS: 4 None

GROUP BY GROUPING: NATION.N_NAME, R_CUSTOMER.C_CUSTKEY, AGGREGATION: SUM(LINEITEM.L_EXTENDEDPRICE * (1

LINEITEM.L_DISCOUNT)) None

CPBTREE INDEX JOIN

INDEX NAME: _SYS_TREE_RS_279_#0_#P0,

INDEX CONDITION: ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY, INDEX FILTER: 'R' = LINEITEM.L_RETURNFLAG

LINEITEM

BTREE INDEX JOIN INDEX 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 SCAN FILTER 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.

INSERT