EXPLAIN
— shows the execution plan for a statement
EXPLAIN [ (explain_option
[,...] ) ]statement
where statement
is any syntactically correct SELECT
, INSERT
, UPDATE
, REPLACE
or DELETE
statement.
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.
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.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
:
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. The default setting is ON.
See Understanding EXPLAIN Command Output for a detailed description of the additional information provided by this option.
OPT_FULL_COST { ON | OFF }
OPT_FULL_COST
is an extension of the functionality provided by the OPT_ESTIMATES
option. In addition to the cost and selectivity estimates shown for the index accesses, use the OPT_FULL_COST
option to show the total cost associated with accessing a given table. This total cost includes the cost for the index accesses and the cost for fetching table records that were filtered by the indexes.
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.
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.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:
EXPLAIN
command for the SQL SELECT
statement.Select List
, which shows the fully qualified names of each of the columns being retrieved.Bitmap index
scan operator which represents a bitmap index scan of the PLAYER_IDX
index.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.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, TEAMEXPLAIN (OPT_ESTIMATES on) SELECT * FROM hockey.hockey WHERE number BETWEEN 20 AND 25;
Select List HOCKEY.HOCKEY.ID (1) HOCKEY.HOCKEY.NUMBER (1) HOCKEY.HOCKEY.NAME (1) HOCKEY.HOCKEY.POSITION (1) HOCKEY.HOCKEY.TEAM (1) Boolean sieve Between HOCKEY.HOCKEY.NUMBER (1) "20" (integer) "25" (integer) Index Scan HOCKEY.HOCKEY (1) Bitmap Index Scan 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 ***