About Explain Plan

You can use the explain command to display what the execution plan will be for a statement without executing the statement. This topic presents and describes several examples.

Using Explain Plan to See the Execution Plan for a Statement

To display the execution plan for a statement without actually executing the statement, use the explain command on the statement:

splice> explain Statement;

Statement

An SQL statement.

Explain Plan and DDL Statements

SQL Data Definition Language (DDL) statements have no known cost, and thus do not require optimization. Because of this, the explain command does not work with DDL statements; attempting to explain a DDL statement such as CREATE TABLE will generate a syntax error. You cannot use explain with any of the following SQL statements:

  • ALTER
  • CREATE … (any statement that starts with CREATE)
  • DROP … (any statement that starts with DROP)
  • GRANT
  • RENAME … (any statement that starts with RENAME)
  • REVOKE
  • TRUNCATE TABLE

Explain Plan Output

When you run the explain command, it displays output in a tree-structured format:

  • The first row in the output summarizes the plan
  • Each row in the output represents a node in the tree.
  • For join nodes, the right side of the join is displayed first, followed by the left side.
  • Child node rows are indented and prefixed with ‘->’.

The first node in the plan output contains these fields:

Field Description
n=number

The number of nodes.

rows=number

The number of output rows.

updateMode=[mode]

The update mode of the statement.

engine=[engineType]

engineType=Spark means this query will be executed by our OLAP engine.

engineType=control means this query will be executed by our OLTP engine.

Each node row in the output contains the following fields:

Field Description
[node label] The name of the node
n=number

The result set number.

This is primarily used internally, and can also be used to determine the relative ordering of optimization.

totalCost=number

The total cost to perform this operation.

This is computed as if the operation is at the top of the operation tree.

processingCost=number

The cost to process all data in this node.

Processing includes everything except for reading the final results over the network.

transferCost=number The cost to send the final results over the network to the control node.
outputRows=number The total number of rows that are output.
outputHeapSize=number unit

The total size of the output result set, and the unit in which that size is expressed, which is one of:

  • B
  • KB
  • MB
  • GB
  • TB
partitions==number

The number of partitions involved.

Partition is currently equivalent to Region; however, this will not necessarily remain true in future releases.

For example:

splice> explain select * from sys.systables t, sys.sysschemas s
        where t.schemaid =s.schemaid;

Plan
-------------------------------------------------------------------------------------------------Cursor(n=5,rows=20,updateMode=READ_ONLY (1),engine=control)
  ->  ScrollInsensitive(n=4,totalCost=21.728,outputRows=20,outputHeapSize=6.641 KB,partitions=1)
    ->  BroadcastJoin(n=3,totalCost=12.648,outputRows=20,outputHeapSize=6.641 KB,partitions=1,preds=[(T.SCHEMAID[4:4] = S.SCHEMAID[4:8])])
      ->  TableScan[SYSSCHEMAS(32)](n=2,totalCost=4.054,outputRows=20,outputHeapSize=6.641 KB,partitions=1)
      ->  TableScan[SYSTABLES(48)](n=1,totalCost=4.054,outputRows=20,outputHeapSize=3.32 KB,partitions=1)

5 rows selected

The next topic, Explain Plan Examples, contains a number of examples, annotated with notes to help you understand the output of each.

See Also