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.

Note: The EXPLAIN command is not part of the SQL standard. Each database vendor has its own idiosyncratic EXPLAIN command output. The NuoDB execution plan is a tree structure and should be interpreted from the bottom up. The exception to this is the JOIN order, which should be interpreted from the top down.

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:

Note: A very useful combination of explain_options for SQL SELECT statements is the EXPLAIN(ANALYZE ON,OPT_ESTIMATES ON) command. These options allow you to compare the actual execution plan costs, calculated by the ANALYZE option, with the estimated execution plan costs, calculated by the OPT_ESTIMATES 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:

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 ***