Open topic with navigation

EXPLAIN

EXPLAIN — shows the execution plan for a statement

Syntax

EXPLAIN [ ( explain_option  [,...] ) ] statement

where statement is any syntactically correct SELECT, INSERT, UPDATE, REPLACE or DELETE statement.

Description

The NuoDB SQL optimizer makes decisions about how to quickly retrieve, join, group or summarize, and sort the rows that the specified SQL statement operates on. These decisions are stored in the form of a query execution plan. This query execution plan is later executed by the query engine. The EXPLAIN command is used to display this query execution plan.

With the information provided in the execution plan, you might choose to update SQL statements or database structure to improve performance. For example, you might decide to add/remove indexes or override the indexes that the optimizer chooses by using hints (see Using Optimizer Hints).

The EXPLAIN command is available to all client connections. You can call it from any client including the NuoDB SQL command line tool.

For details about the EXPLAIN command output and how to interpret the information that it provides, including some examples, see Understanding EXPLAIN Command Output.

Parameters

explain_option

To obtain additional information about the execution plan, specify one or more of the parameters described below. The default is that each of these parameters is set to OFF. To provide explain_option:

• Specify one or more parameters in any order.
• Separate multiple parameters with a comma.
• Enclose the parameter(s) in parentheses.
• Optionally parameterize the ON or OFF setting in the same way that you would specify parameters in a SQL statement.

OPT_ESTIMATES { ON | OFF }

Set OPT_ESTIMATES to ON to cause the EXPLAIN command output to include cost estimates for each index or table search that the optimizer chooses for the execution plan. See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

ANALYZE { ON | OFF }

Set ANALYZE to ON to cause the EXPLAIN command to evaluate the SQL  statement, but instead of calculating cost estimates based on index statistics that NuoDB generates, this ANALYZE option forces the SQL statement to be executed. This allows the query engine to collect actual statistics for each operator of the execution plan. And because the SQL statement is executed, the execution time per plan operator is also collected. These statistics are displayed in EXPLAIN format annotated with these counter and time information per plan operator.

This ANALYZE option supports only SQL SELECT statements. See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

EXPLAIN (ANALYZE ON) UPDATE hockey SET team = 'Boston' WHERE id = 1;
Explain analyze on statement other than select or union is not supported.  Found: Update

PARTITION_IDS { ON | OFF }

Set PARTITION_IDS to ON to cause the EXPLAIN command output to list the table partition IDs for each table partition that the optimizer searched to retrieve rows (see Use Cases for Table Partitions and Storage Groups). This ID value is displayed for only table(s) that are partitioned. This option is ignored for table(s) that are not partitioned. See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.

Sample Output

The following shows sample output from the EXPLAIN command. This output is displayed for a very simple SQL SELECT statement with a predicate (WHERE clause) and sorting (ORDER BY clause). See Understanding EXPLAIN Command Output for more examples and a more detailed explanation of the output.

This EXPLAIN command output shows the following:

• The EXPLAIN command for the SQL SELECT statement.
• The Select List, which shows the fully qualified names of each of the columns being retrieved.
• An Inversion operator which represents a Bitmap index scan of the PLAYER_IDX index.
• The cost estimate values, which display based on the OPT_ESTIMATES option being set to ON. This shows a 16% selectivity ratio based on the number of rows being returned by the index scan.
• A Boolean sieve Between operator, which represents the sql statement predicate, as defined by the WHERE clause "hockey.number BETWEEN 20 AND 25".

SHOW TABLE hockey.hockey;
    Found table HOCKEY in schema HOCKEY
        Fields:
            ID bigint
                Nullable: No
                Generator: HOCKEY$IDENTITY_SEQUENCE    Generated Always
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
        Primary Index: HOCKEY..PRIMARY_KEY on field: ID
        Unique Index: PLAYER_IDX on fields: NUMBER, NAME, TEAM 

EXPLAIN (OPT_ESTIMATES on) SELECT * FROM hockey.hockey 
                             WHERE number BETWEEN 20 AND 25;
 Select
  List
    Field HOCKEY.HOCKEY.ID (1)
    Field HOCKEY.HOCKEY.NUMBER (1)
    Field HOCKEY.HOCKEY.NAME (1)
    Field HOCKEY.HOCKEY.POSITION (1)
    Field HOCKEY.HOCKEY.TEAM (1)
  Boolean sieve
    Between
      Field HOCKEY.HOCKEY.NUMBER (1)
      "20" (integer) 
      "25" (integer) 
    Inversion HOCKEY.HOCKEY  (1)
      Bitmap index PLAYER_IDX [cost: 32.6, interval sel: 16.000%, predicate sel: 16.000%, keys: 5, rows: 5]
        "20" (integer) 
        *** null ***
        *** null ***
        "25" (integer) 
        *** null ***
        *** null ***