Understanding EXPLAIN Command Output

The purpose of reading the output from the NuoDB EXPLAIN command (see EXPLAIN) is to see the execution plan that the optimizer will choose to quickly retrieve, join, group or summarize, and sort the rows that the SQL statement operates on or returns. The SQL statement can be a SELECT statement or any DML statement that requires the optimizer to first retrieve the rows to execute for the DML operation.

The NuoDB EXPLAIN command output displays the execution plan as a hierarchical list of operators and the stream of records that result from applying those operators. When you read the plan from top to bottom you can see the order in which NuoDB SQL forms the plan. When you read the plan from bottom to top you see the plan as the query engine will execute it. The exception to this is the JOIN order. This should be interpreted from top to bottom.

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 output provides the following information:

  • SQL statement - this is the type of SQL statement. This can be any one of the following: SELECT, INSERT, UPDATE, REPLACE or DELETE.

  • list of fields- the columns being selected, updated, replaced, deleted - the projection.

  • execution plan - operator tree generated by the optimizer. The execution plan takes a set of inputs, displayed as sub-trees, and returns a stream of records for processing the SQL statement.

Execution Plan Top Level Operators

The execution plan is a hierarchical sequence of operators. The top level operators are:

Operator Description

Table Scan

Represents a full table scan. Either there is no index, or the indexes defined are not usable based on the predicates in the WHERE clause, or using the index would be too costly for the optimizer. The optimizer would determine a potential index to be too costly if, based on the predicates, the majority of rows in the table would be retrieved. In this case, a full table scan would be more efficient.

This operator has no input (no sub-tree is displayed) and the stream of records it outputs represents the entire table.

Index scan

Represents the usage of an index access method. There are various access methods that an Inversion operator can use to scan an index. Input to this operator can be a bitmap of record IDs or a list of key values. This operator outputs a stream of records. The order of the records is dependent on the Index Access Method. See Index Ordering Properties.

Boolean sieve

Represents any predicate in the WHERE clause. This operator can be thought of as a filter (like a sieve) that applies boolean conditions to output only those records that satisfy the condition[s]. It always takes two inputs, displayed as sub-trees. The first input is a series of boolean conditions and the second input is a record stream on which to apply those conditions. Output from the Boolean sieve operator is a record stream containing only records that satisfy the boolean condition.
Possible boolean conditions for the first input are:

  • And, Or, Eql, Not, Gtr, Lss, Neq, etc.

  • Functions, such as Upper, Lower, Sum, Min, Max, etc.

  • In List (DynamicInList, ConstantInList, ScanInList)

  • In Select

  • Like

  • Between

This is not a comprehensive list, but is meant to give you an idea of what types of inputs are possible.

Nested Loop Join

Represents the method that the optimizer will use to join two or more tables based on the predicate conditions of the JOIN clause.

This operator takes as input two or more boolean operators, each representing certain conjuncts in a JOIN clause and each specific to a certain table used in that JOIN clause. Output from the Join operator is a stream of records, from both tables, that match the JOIN criteria.

Description of Bitmap Index Operators

A bitmap index operator represents an index intersection and union (IIU) plan that takes one or more index scans as input and applies intersection or union operations for search conditions based on the conjuncts in the query predicate of the WHERE clause. The output of this plan is a bitmap of record IDs. There are various types of bitmap index operators in an execution plan and they are described in the following table.

Bitmap Index Operators Description

Bitmap

This bitmap represents a scan over an interval of an index key where the interval is deduced from the conditions in the query predicate. For each conjunct in the predicate that uses the index generating this bitmap, there are one or more sub-trees displayed - one node if the boundary is equality and two nodes (min,max) if the boundary is less than or greater than.

BitmapAnd

This bitmap represents a scan on an index when AND is part of the query predicate and two or more separate indexes are used. It has two or more sub-trees displayed, each representing a Bitmap Index Operator for the indexes used for the operands of the AND.

BitmapOr

This bitmap represents a scan on an index when OR is part of the query predicate and two or more separate indexes are used. It has two or more sub-trees displayed, each representing a Bitmap Index Operator for the indexes used for the operands of the OR.

InSelectBitmap

This bitmap represents a scan on an index of the outer table, using all the values of the subquery. It has two subtrees displayed:

  • the column list of the outer table over which the Index scan is performed.

  • the execution plan of the IN subquery.

LikeBitmap

This bitmap represents an Index scan when LIKE is part of the query predicate. It has one subtree displayed which represents the constant string used in the LIKE clause, to which the field in the index is bound.

MinMaxBitmap

This bitmap represents an Index scan when either MIN or MAX is part of the SELECT column_list. It has one subtree displayed, but this is ignored and is always null.

Index Ordering Properties

A bitmap index fetches records in record ID order. The records are then processed based on the GROUP BY and ORDER BY clauses.

A streaming index fetches records in index key order. The optimizer can make use of this ordering property to optimize the processing of GROUP BY and ORDER BY clauses of the SQL statement. If the order of records output by the streaming index matches the order specified by the ORDER BY clause of the SQL statement, then the optimizer will completely optimize out the sort from the execution plan.

A streaming covering index is similar to a streaming index in that it also fetches records in index key order. The optimizer will choose this index method if the index contains all of the columns that are to be retrieved. Therefore, the optimizer can make use of covering indexes to retrieve the result set information (without fetching table records) and optimize the processing of both the GROUP BY and the ORDER BY clauses.

Query Participants

Query participants as predicates in the WHERE clause are represented in the tree as follows:

Node Participant

Parameter

A parameter that is passed to the SQL statement at runtime

Constant

A quoted string (even for numeric values)

Description of Optimizer Parameters

The EXPLAIN command, in addition to displaying the execution plan, can provide additional information that the optimizer uses to chose the most efficient execution plan for a SQL statement. See EXPLAIN for the command usage. This section describes this additional information provided by each of these EXPLAIN command options.

Cost Estimates

Running the EXPLAIN command with the option OPT_ESTIMATES set to ON provides optimizer cost estimates for each index that the optimizer choose to scan. These are the costs associated with that execution plan. The lower the cost, the more efficient the plan. These costs are based on statistics that are computed for each index and stored in NuoDB system tables (see Improving Query Performance). These estimates are computed internally by the optimizer and cannot be changed. See Example 6 below.

In order for the optimizer to chose the most efficient execution plan, the index statistics should be kept up-to-date to reflect changes to the underlying table rows (i.e. rows being inserted, updated or deleted). See Improving Query Performance for information about the NuoDB system tables that store these index statistics. To update the index statistics, run the ANALYZE command. This ANALYZE command should be run for every table in the SQL statement being passed to the EXPLAIN command. Alternatively, it can be run for each index on every table in the SQL statement (see ANALYZE command).

Some of the information provided by these cost estimates include:

Type of Estimate

Description

cost

This represents an abstract measure of how expensive an execution plan is. It is currently measured in I/O cost, in terms of bytes read.

total access cost

This represents an abstract measure for index access and data fetch cost; this is only available with the OPT_FULL_COST (Optimizer Full Cost) option.

interval sel

This represents the percentage of the total number of keys that an Index scan will fetch. It is called interval sel because the lower and/or upper boundaries of an Index scan define an interval over the whole index.

predicate sel

This represents the additional selectivity of any predicates over other columns of an index that could not be used for the Index scan itself. This is usually the same as interval sel as quite often there are no additional predicates.

keys

This represents the number of rows that an Index scan will output.

rows

This represents an estimated number of result (output) rows that the optimizer retrieve for that particular operation. For example, if it is a full table scan, this would be the total number of rows in the table. If it is an index, this would be the number of rows retrieved by the index search operation.

If any of these estimates are shown as -1, this means that the optimizer was not able to compute a cost estimate for that particular operator.

The selectivity ratios and the keys estimate are calculated and displayed if the optimizer chooses to use an index.

Analyze Cost Statistics

The ANALYZE option of the EXPLAIN command evaluates 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 optimizer to collect actual statistics for each operator of the execution plan. These statistics are displayed in EXPLAIN format annotated with the counters' information.

Unlike other EXPLAIN command options, the ANALYZE option supports only SQL SELECT statements. The primary reason is that the ANALYZE option causes the SQL statement to be executed. Therefore, running the EXPLAIN command with the ANALYZE option on a DML statement may accidentally modify the database.

If a SQL SELECT statement has bind parameters NuoDB may dynamically re-optimize the execution plan at run time, when the parameters values are provided. This could cause the optimizer to chose a different execution plan from the one displayed by the EXPLAIN command output. The ANALYZE option is very useful in this case, as it shows the actual execution plan being evaluated.

When the ANALYZE option is set to ON, the EXPLAIN command output displays execution plan operators and their values as a comma-separated list enclosed in parentheses. These include:

micros

This is the total time, in microseconds, that this node took during execution.

f/o

This is the time, in microseconds, spent in "fetch" calls.
This is the time, in microseconds, spent in "open" calls.
The sum of these two values equals the micros value.

rows in

This is the number of records/keys that are input to the operator.

rows out

This is the number of records/keys produced as output by the operator.

rows in / rows out

This is the actual selectivity of the operator, comparing the number of rows that are input to the operator versus the number of rows produced as output by the operator.

Understanding Disk Spill

Processing blocks that support Spill to Disk (also known as Disk Spill) functionality also provide memory buffering data which can be used to better understand when and why Disk Spill has been used during query execution. Compatible processing blocks may print the following:

total memory

The total memory required to process the block.

peak memory

The peak memory required by a single block, when multiple passes are used to process the block.

Disk Spill

Indicates whether Spill to Disk functionality was used during processing.

spill threshold

The memory threshold for the processing block, when exceeded Disk Spill is triggered. For more information, see DISKSPILL_MEMORY_THRESHOLD_MB in SQL System Properties.

Disk Spill due to memory pressure

Indicates that Spill to Disk functionality was used during processing. It was triggered before DISKSPILL_MEMORY_THRESHOLD_MB was reached due to memory pressure in the transaction engine.

Optimizer Partition Pruning

NuoDB supports table partitioning (see Using Table Partitions and Storage Groups). The table partition criteria is stored in NuoDB system tables. The system table stores the column that the table is partitioned on. The system table also stores the partition criteria for each table partition (see PARTITIONIDS System Table Description and PARTITIONCRITERIA System Table Description). With a SQL statement predicate that includes this partitioning column, the optimizer can compare the predicate value to the partition criteria for each table partition. This gives the optimizer the ability to ignore, or "prune", one or more table partitions when searching for table rows that match the predicate values.

When the EXPLAIN command PARTITION_IDS option is set to ON, the EXPLAIN command output will display, for each table that is partitioned, the SYSTEM.PARTITIONIDS.PARTITIONID value for each partition that the optimizer searched. This option is ignored for tables that are not partitioned (see Example 14).

The Coerce Operator

The COERCE operator is the operator which performs the implicit conversions decided by the Data Type Conversion mechanism. The presence of this operator in a query plan indicates that the initial query contain discrepancies in terms of types which were corrected automatically by the NuoDB query compiler. The COERCE operator is functionally similar to the CAST operator, but is not available as a separate call (it is only used internally). Unlike the CAST operator which performs truncation and/or rounding, in certain cases when the value to be converted does not fit in the target type, the COERCE operator is designed to be lossless and throws an error if the conversion would require truncation and/or rounding.

Example:

CREATE TABLE t(col decimal(4, 2));
INSERT INTO t(col) values(1.0);
INSERT INTO t(col) values(1.0);
EXPLAIN SELECT SUBSTRING('nuodb', col) from t;
Select
  List
    Alias:SUBSTR
    Substr
      "nuodb"
        Coerce
        T.COL
         as integer
  Table Scan T  [cost: 33.00, rows: 11.00]
In the example above, the column col has type decimal(4,2) but the substring expression expects its second argument to be of type integer. The NuoDB query compiler is implicitly converting that argument to an integer type.

About Execution Plans

A few things to note when looking at execution plans:

  • Full Table scans are often (but not always) to be avoided. Indexes can be added to tables based on columns used in the WHERE clause. However, adding indexes can have a negative impact on SQL DML statement performance.

  • Index hints can be used to override indexes that the optimizer might choose (see Using Optimizer Hints).

  • The output can display additional information which the optimizer computes based on options that can be specified for the EXPLAIN command. See EXPLAIN command for a detailed description of these options and the information that it displays. Several of the examples below show this additional information.

  • Updating index statistics can provide the optimizer with more accurate cost estimates (see ANALYZE).

  • In multiple table joins, the join order is very important. Poor join order selection can make a query very slow. It is the job of the optimizer to choose the best join order.

  • The optimizer attempts to coerce query constants to the appropriate types to compare against fields in a table. Make sure the constants are of the appropriate data types.

The output from the EXPLAIN command gives a complete view of the plan that the optimizer produced. However, you should not expect the execution plan to stay static throughout the life of a database. Some reasons for this are:

  • As tables shrink and grow relative to other tables the optimizer could make different decisions about the best execution plan.

  • Different processes (transaction engines) in a database may have slightly different (newer) information about tables and indexes. The execution plan generated may depend on to which TE the client is connected.

Examples of EXPLAIN Command Output

Example 1: Simple EXPLAIN command output with full table scan

The simplest execution plan is one for which there is no index. A simple execution plan is generated from the following query:

EXPLAIN SELECT * FROM hockey.players;

This results in the following a Select operator with inputs:

  • a List operator, representing the list of columns being selected

  • an operator, meaning no indexes are available and a full table scan is done. The Table Scan operator returns a stream of records representing the entire HOCKEY.PLAYERS table. There is no Boolean sieve operator because there were no predicates, defined by a WHERE clause, provided.

    The EXPLAIN command output for such an execution plan looks as follows.

    EXPLAIN SELECT * FROM hockey.players;
    Select
      List
         HOCKEY.PLAYERS.PLAYERID (1)
         HOCKEY.PLAYERS.FIRSTNAME (1)
         HOCKEY.PLAYERS.LASTNAME (1)
         HOCKEY.PLAYERS.HEIGHT (1)
         HOCKEY.PLAYERS.WEIGHT (1)
         HOCKEY.PLAYERS.FIRSTNHL (1)
         HOCKEY.PLAYERS.LASTNHL (1)
         HOCKEY.PLAYERS.POSITION (1)
         HOCKEY.PLAYERS.BIRTHYEAR (1)
         HOCKEY.PLAYERS.BIRTHMON (1)
         HOCKEY.PLAYERS.BIRTHDAY (1)
         HOCKEY.PLAYERS.BIRTHCOUNTRY (1)
         HOCKEY.PLAYERS.BIRTHSTATE (1)
         HOCKEY.PLAYERS.BIRTHCITY (1)
      Table scan HOCKEY.PLAYERS  (1)
Example 2: EXPLAIN command output for SQL statement with Predicate

This example uses the same SQL SELECT statement as the previous example and adds a predicate. However, the column in the WHERE clause is not indexed.

EXPLAIN SELECT * FROM players WHERE firstname = 'Mike';

This also results in a Select operator with inputs

  • a List operator, representing the list of columns being selected

  • a Boolean sieve operator. The Boolean sieve operator has the following as input:

    • a Table scan operator. The Table Scan operator returns a stream of records representing the entire HOCKEY.PLAYERS table.

    • an Eql condition. The Eql condition is applied on the stream of records returned from the Table Scan operator.

      The Boolean sieve operator returns a stream of records with only those records where “FIRSTNAME='Mike'” is true.

      The EXPLAIN command output for such an execution plan looks as follows.

      EXPLAIN SELECT * FROM players WHERE firstname = 'Mike';
      Select
        List
           PLAYERS.PLAYERID
          ....
           PLAYERS.BIRTHCITY
        Boolean sieve
          Eql
            PLAYERS.FIRSTNAME
            "Mike" (varchar)
          Table scan PLAYERS  [cost: 315840.00, rows: 7520.00]
Example 3: EXPLAIN command output for SQL statement with Index

Let’s modify the query, adding a predicate on PLAYERID, so that it uses the primary key index on the PLAYERS PLAYERID field as in:

EXPLAIN SELECT * FROM players WHERE PLAYERID >= 'a';
Select
  List
     HOCKEY.PLAYERS.PLAYERID
     ...
     HOCKEY.PLAYERS.BIRTHCITY
  Boolean sieve
    Geg
       PLAYERS.PLAYERID
      "a"
    Table scan HOCKEY.PLAYERS  [cost: 315840.00, rows: 7520.00]

This also results in a Select operator with inputs:

  • a List operator, representing the list of columns being selected

  • a Boolean sieve operator. The Boolean sieve operator has as input:

    • a Table scan operator. The Table Scan operator returns a stream of records representing the entire HOCKEY.PLAYERS table

    • a Geq condition. The Geq condition is applied on the stream of records returned from the Table Scan operator

      The Boolean sieve operator returns a stream of records with only those records where “PLAYERID >= 'a'” is true

      What is interesting is that the EXPLAIN command output is still showing a Table scan operator on the PLAYERS table, even though we are providing a predicate based on the column defined by the primary key index. Why does the optimizer choose to not use the unique primary key index? The optimizer has the benefit of knowing, based on the index statistics, that all of the rows in the table will return a true evaluation of the predicate. In this case, the optimizer computes a cost estimate for a full table scan to be more efficient than a cost estimate for an Index scan.

      What if the predicate is modified to reduce the number of rows evaluating to true as compared to the total number of rows. At some point, the optimizer will compute the cost of an Index scan to be more efficient than the cost of a full table scan. When the cost estimate for the index becomes more efficient than the cost estimate for the full table scan, the optimizer will choose to perform an Index scan.

      EXPLAIN SELECT * FROM players WHERE playerid > 'c';
      Select
        List
           HOCKEY.PLAYERS.PLAYERID (1)
           HOCKEY.PLAYERS.FIRSTNAME (1)
           HOCKEY.PLAYERS.LASTNAME (1)
           HOCKEY.PLAYERS.HEIGHT (1)
           HOCKEY.PLAYERS.WEIGHT (1)
           HOCKEY.PLAYERS.FIRSTNHL (1)
           HOCKEY.PLAYERS.LASTNHL (1)
           HOCKEY.PLAYERS.POSITION (1)
           HOCKEY.PLAYERS.BIRTHYEAR (1)
           HOCKEY.PLAYERS.BIRTHMON (1)
           HOCKEY.PLAYERS.BIRTHDAY (1)
           HOCKEY.PLAYERS.BIRTHCOUNTRY (1)
           HOCKEY.PLAYERS.BIRTHSTATE (1)
           HOCKEY.PLAYERS.BIRTHCITY (1)
        Boolean sieve
          Gtr
             HOCKEY.PLAYERS.PLAYERID (1)
            "c" (varchar)                          # We are comparing against a value "c"
          Index scan HOCKEY.PLAYERS  (1)            # Note: correlation to table HOCKEY.PLAYERS
            Bitmap Index scan PLAYERS..PRIMARY_KEY      # Note: correlation to index PLAYERS..PRIMARY_KEY
              "c" (varchar)                        # Lower bound: "c"
              *** null ***                         # Upper bound: none

      Note the correlation from the Inversion operator to the table HOCKEY.PLAYERS and the correlation from the Bitmap Index to the index PLAYERS..PRIMARY_KEY.

      If an upper bound is supplied to the SQL SELECT statement, you’ll see a few differences in the output:

      EXPLAIN SELECT * FROM players WHERE playerid > 'a' AND playerid < 'b';
      Select
        List
           HOCKEY.PLAYERS.PLAYERID (1)
          ....
           HOCKEY.PLAYERS.BIRTHCITY (1)
        Boolean sieve
          And                                     # New: the ANDing of the Lss and Gtr Nodes
            Gtr
               HOCKEY.PLAYERS.PLAYERID (1)
              "a" (varchar)
            Lss                                   # New less than node
               HOCKEY.PLAYERS.PLAYERID (1)
              "b" (varchar)
          Index scan HOCKEY.PLAYERS  (1)
            Bitmap Index scan PLAYERS..PRIMARY_KEY
              "a" (varchar)
              "b" (varchar)                       # An upper bound has been added
Example 4: EXPLAIN command output with a JOIN clause

Now let’s try a JOIN clause. Let’s select all of the players from the 2011 Stanley Cup Champions: The Boston Bruins. The execution plan for this SQL statement is as follows:

EXPLAIN SELECT P.FIRSTNAME, P.LASTNAME FROM PLAYERS P, SCORING S
     WHERE S.PLAYERID = P.PLAYERID AND S.YEAR = 2011 AND S.TEAMID = 'BOS';
Select
  List
     PLAYERS.FIRSTNAME
     PLAYERS.LASTNAME
  Nested Loop Join
    Table Scan PLAYERS P [COST: 315840.00, ROWS 7520.00]
    Index Scan SCORING S (SHALLOW)
      Bitmap Index Scan SCORING..PRIMARY_KEY [cost: 9.8, interval sel: 0.002%,
      predicate sel: 0.002%, keys: 1, rows: 1]
                PLAYERS.PLAYERID
                "2011"
                *** null ***
                "BOS"
                *** null ***

Again, we have a Select operator with inputs:

  • A List operator, showing each of the columns being selected

  • A Join operator.

    The Join operator has as input:

  • A full table scan of the PLAYERS table

  • An Index scan operator, showing a Bitmap Index scan scan of the PRIMARY KEY index on the SCORING table, using the JOIN predicate defined by the playerid column in both the PLAYERS and SCORING table. This Index scan operator also evaluates the predicate, as defined by the WHERE clause, but may return more keys than requested and, in some cases, also returns keys that are equal to the predicate boundary. This will be re-evaluated again by the Boolean sieve operator.

    The Boolean sieve operator has as its input the record stream that is the output from the JOIN operator and covers the “scoring.year = 2011 and scoring.teamid = 'BOS'” predicate defined by the WHERE clause.

Example 5: EXPLAIN command output with JOIN clause with Index

In the previous example, the full table scan on the PLAYERS table is something we may want to look at. If this was a really big table it could take a considerable amount of time to process. A review of the data model shows that a composite index on both the TEAMID and YEAR columns of the SCORING table would allow for a more direct access based on the predicate, as defined by the WHERE clause.

CREATE INDEX scoring_year_teamid_idx ON scoring(teamid,year);
ANALYZE INDEX scoring_year_teamid_idx;

EXPLAIN SELECT p.firstname, p.lastname FROM players p, scoring s
          WHERE s.playerid = p.playerid AND s.year = 2011 AND s.teamid = 'BOS';
 Select
  List
     HOCKEY.PLAYERS.FIRSTNAME (1)
     HOCKEY.PLAYERS.LASTNAME (1)
  Nested Loop Join
    Boolean sieve
      And
        Eql
           HOCKEY.SCORING.YEAR (2)
          "2011" (integer)
        Eql
           HOCKEY.SCORING.TEAMID (2)
          "BOS" (varchar)
      Index scan HOCKEY.SCORING S (2)
        Bitmap Index scan SCORING_YEAR_TEAMID_IDX
          "BOS" (varchar)
          "2011" (integer)
    Boolean sieve
      Eql
         HOCKEY.SCORING.PLAYERID (2)
         HOCKEY.PLAYERS.PLAYERID (1)
      Index scan HOCKEY.PLAYERS P (1)
        Bitmap Index scan PLAYERS..PRIMARY_KEY
           HOCKEY.SCORING.PLAYERID (2)

After the new index is added, the EXPLAIN command output for this same SQL statement now shows the same inputs to the Select, a List operator and a Join operator. However, the Join operator has changed. Its two inputs are new defined as follows:

  • An Index scan operator for the Bitmap Index scan scan of the SCORING table, using this new composite index. The Boolean sieve covers the predicate, as defined by the WHERE clause.

  • Another Index scan operator for the scan Bitmap Index scan of the PLAYERS table. The Boolean sieve covers the JOIN predicate, as defined the playerid columns of both the SCORING and PLAYERS tables.

    The overall benefit of this new execution plan is reduced number of input records for the Join operator. This should result in less execution time.

Example 6: EXPLAIN command output with Optimizer Estimates displayed

Let’s look at this same SQL statement with Index Statistics turned on. Remember to run ANALYZE on all tables involved in the SQL statement.

ANALYZE TABLE SCORING;
ANALYZE TABLE PLAYERS;
EXPLAIN (OPT_ESTIMATES ON) SELECT p.firstname, p.lastname FROM players p, scoring s
              WHERE s.playerid = p.playerid AND s.year = 2011 AND s.teamid = 'BOS';
 Select
  List
     HOCKEY.PLAYERS.FIRSTNAME (1)
     HOCKEY.PLAYERS.LASTNAME (1)
  Nested Loop Join
    Boolean sieve
      And
        Eql
           HOCKEY.SCORING.YEAR (2)
          "2011" (integer)
        Eql
           HOCKEY.SCORING.TEAMID (2)
          "BOS" (varchar)
      Index scan HOCKEY.SCORING S (2)
        Bitmap Index scan SCORING_YEAR_TEAMID_IDX [cost: 136.2, interval sel: 0.065%, predicate sel: 0.065%, keys: 30, rows: 30]
          "BOS" (varchar)
          "2011" (integer)
    Boolean sieve
      Eql
         HOCKEY.SCORING.PLAYERID (2)
         HOCKEY.PLAYERS.PLAYERID (1)
      Index scan HOCKEY.PLAYERS P (1)
        Bitmap Index scan PLAYERS..PRIMARY_KEY [cost: 3.0, interval sel: 0.013%, predicate sel: 0.013%, keys: 1, rows: 1]
           HOCKEY.SCORING.PLAYERID (2)

This first set of optimizer estimates is for the SCORING_YEAR_TEAMID_IDX index on the SCORING table:
[cost: 136.2, interval sel: 0.065%, predicate sel: 0.065%, keys: 30, rows: 30]

This indicates that the total cost estimate to execute this Index scan is 136.2. This is an abstract number and is only meaningful relative to other SQL statement operations. In general, the lower the number the better.

This estimate also tells us that both interval and predicate selectivity is 0.065%, which means 0.065% of all record IDs (an estimated 30 rows) available in the table SCORING are percolated up to the Inversion and Boolean sieve operators.

The number of key values, and the number of rows returned in the output stream, are estimated at 30. This is only an estimated. Actual rows returns could be more or less.

The second optimizer index estimate for the PRIMARY KEY index on the PLAYERS table:
[cost: 3.0, interval sel: 0.013%, predicate sel: 0.013%, keys: 1, rows: 1]

This indicates that the cost estimate to execute this Index scan is 3.0. Again, this is an abstract number and is only meaningful relative to other SQL statement operations.

This estimate also tells us that both interval and predicate selectivity is 0.013%, which means 0.013% of all records IDs (an estimated 1 row) available in the table PLAYERS is percolated up to the Inversion and Boolean sieve operators.

The number of key values, and the number of rows returned in the output stream, are estimated at 1. Again, this is only an estimate.

Example 6a: EXPLAIN command output with total access cost displayed

As well as index access costs, the following example shows the total access cost associated with the SCORING table.

EXPLAIN (OPT_FULL_COST ON) SELECT playerid, year, teamid, goals FROM scoring WHERE playerid = 'rydermi01' and year in (2008, 2009)
Select
List
    SCORING.PLAYERID
    SCORING.YEAR
    SCORING.TEAMID
    SCORING.GOALS
Boolean sieve
    And
      Eql
         SCORING.PLAYERID
        "rydermi01"
      ConstantInList
        SCORING.YEAR
        List
          "2008"
          "2009"
    Index Scan SCORING  [Total access cost: 78.4]
      BitmapOr [cost: 19.6, interval sel: 0.004%, predicate sel: 0.004%, keys: 2, rows: 2]
      Bitmap Index Scan SCORING..PRIMARY_KEY [cost: 9.8, interval sel: 0.002%, predicate sel: 0.002%, keys: 1, rows: 1]
          "rydermi01"
          "2008"
          *** null ***
          *** null ***
          *** null ***
      Bitmap Index Scan SCORING..PRIMARY_KEY [cost: 9.8, interval sel: 0.002%, predicate sel: 0.002%, keys: 1, rows: 1]
          "rydermi01"
          "2009"
          *** null ***
          *** null ***
          *** null ***

The total access cost includes the cost for the index accesses and the cost for fetching table records that were filtered by the indexes.

Example 7: EXPLAIN command output with ANALYZE option

For the purpose of this example, we create a table with multiple indexes:

CREATE TABLE testtab (column1 INTEGER, column2 INTEGER, column3 INTEGER, column4ni INTEGER);
INSERT INTO testtab VALUES (1,1,1,1), (2,2,2,2), (3,3,3,3), (4,4,4,4), (5,5,5,5);
INSERT INTO testtab SELECT column1+5,  column2+5,  column3+5,  column4ni+5  FROM testtab;
INSERT INTO testtab SELECT column1+10, column2+10, column3+10, column4ni+10 FROM testtab;
INSERT INTO testtab select column1+15, column2+15, column3+15, column4ni+15 FROM testtab;
INSERT INTO testtab select column1+20, column2+20, column3+20, column4ni+20 FROM testtab;
INSERT INTO testtab select column1+25, column2+25, column3+25, column4ni+25 FROM testtab;
INSERT INTO testtab select column1+30, column2+30, column3+30, column4ni+30 FROM testtab;
INSERT INTO testtab select column1+35, column2+35, column3+35, column4ni+35 FROM testtab;

CREATE INDEX idx_testtab_column1 ON testtab (column1);
CREATE INDEX idx_testtab_column2 ON testtab (column2);

ANALYZE TABLE testtab;

The following execution plan is generated statically based on default statistics, without parameter knowledge. In other words, the cost estimates are exactly the same, regardless of the parameter values that are substituted. In addition, the costs that the optimizer calculates are estimated costs. These estimates are based on the index statistics that are generated by the ANALYZE command. When the SQL statement executes at runtime, and the parameter values are substituted, the optimizer could calculate different cost estimates that can change the execution plan.

EXPLAIN (opt_estimates on)
SELECT count(*) from (
   SELECT * FROM testtab
    WHERE (column1 BETWEEN ? AND ?
       OR column1 BETWEEN ? AND ?)
      AND column2 < ?);

Enter value: 2
Enter value: 143
Enter value: 3
Enter value: 144
Enter value: 50

 Select
  List
    Count
  Boolean sieve
    And
      Or
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          CastTo(integer)
            Parameter (1)
          CastTo(integer)
            Parameter (2)
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          CastTo(integer)
            Parameter (3)
          CastTo(integer)
            Parameter (4)
      Lss
         HOCKEY.TESTTAB.COLUMN2 (2)
        CastTo(integer)
          Parameter (5)
    Index scan HOCKEY.TESTTAB  (2)
      BitmapAnd [cost: 1598.3, interval sel: 6.796%, predicate sel: 6.796%, keys: -1, rows: -1]
        BitmapOr [cost: 424.7, interval sel: 20.594%, predicate sel: 20.594%, keys: -1, rows: -1]
          Bitmap Index scan IDX_TESTTAB_COLUMN1 [cost: 212.3, interval sel: 10.890%, predicate sel: 10.890%, keys: 70, rows: 70]
            CastTo(integer)
              Parameter (1)
            CastTo(integer)
              Parameter (2)
          Bitmap Index scan IDX_TESTTAB_COLUMN1 [cost: 212.3, interval sel: 10.890%, predicate sel: 10.890%, keys: 70, rows: 70]
            CastTo(integer)
              Parameter (3)
            CastTo(integer)
              Parameter (4)
        Bitmap Index scan  IDX_TESTTAB_COLUMN2 [cost: 643.5, interval sel: 33.000%, predicate sel: 33.000%, keys: 214, rows: 214]
          *** null ***
          CastTo(integer)
            Parameter (5)

With the EXPLAIN command option ANALYZE set to ON, the output will show the execution plan that represents the query engine statistics/counters obtained from the actual execution. This execution plan is generated dynamically by the optimizer and takes into account the parameter values that have been substituted at execution time. The optimizer computes these costs dynamically by collecting actual statistics for each operator of the execution plan. This execution plan is much more realistic.

EXPLAIN (OPT_ESTIMATES ON, ANALYZE ON)
SELECT count(*) from (
   SELECT * FROM testtab
    WHERE (column1 BETWEEN ? AND ?
       OR column1 BETWEEN ? AND ?)
      AND column2 < ?);

Enter value: 2
Enter value: 143
Enter value: 3
Enter value: 144
Enter value: 50

 Select
  List
    Count
  Boolean sieve (micros: 297, rows out: 143, rows in: 150, out/in: 0.9533)
    And
      Or
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          CastTo(integer)
            Parameter (1)
          CastTo(integer)
            Parameter (2)
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          CastTo(integer)
            Parameter (3)
          CastTo(integer)
            Parameter (4)
      Lss
         HOCKEY.TESTTAB.COLUMN2 (2)
        CastTo(integer)
          Parameter (5)
    Index scan HOCKEY.TESTTAB  (2) (micros: 181 (f/o=157/24), rows out: 150, batches: 12)
      Bitmap Index scan IDX_TESTTAB_COLUMN2 [cost: 2471.1, interval sel: 25.344%, predicate sel: 25.344%, keys: 164, rows: 164]
        *** null ***
        CastTo(integer)
          Parameter (5)

If we eliminate the runtime parameters, by substituting the parameter values as constants into the SQL statement, the EXPLAIN command output below shows the static execution plan generated with these constant values. This is the exact same plan as the previous execution plan, which was dynamically generated because of the EXPLAIN command option ANALYZE. This is a different plan than the static execution plan that was generated without the ANALYZE option.

EXPLAIN (OPT_ESTIMATES ON, ANALYZE ON)
SELECT count(*) from (
   SELECT * FROM testtab
    WHERE (column1 BETWEEN 2 AND 143
       OR column1 BETWEEN 3 AND 144)
      AND column2 < 50);

 Select
  List
    Count
  Boolean sieve (micros: 273, rows out: 143, rows in: 144, out/in: 0.9931)
    And
      Or
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          "2" (integer)
          "143" (integer)
        Between
           HOCKEY.TESTTAB.COLUMN1 (2)
          "3" (integer)
          "144" (integer)
      Lss
         HOCKEY.TESTTAB.COLUMN2 (2)
        "50" (integer)
    Index scan HOCKEY.TESTTAB  (2) (micros: 175 (f/o=150/25), rows out: 144, batches: 12)
      Bitmap Index scan IDX_TESTTAB_COLUMN2 [cost: 2471.1, interval sel: 25.344%, predicate sel: 25.344%, keys: 164, rows: 164]
        "-2147483648" (integer)
        "49" (integer)
Example 8: Other Index Scans

In the above examples, the index scans were based on simple or range predicate values. There are other types of index scans. If the SQL LIKE predicate is being used on an indexed field, the optimizer will try to take advantage of the index. In the example below you will see the introduction of a new type of bitmap, the LikeBitmap operator.

EXPLAIN SELECT firstname, lastname FROM players WHERE playerid LIKE 'b%';
 Select
  List
     HOCKEY.PLAYERS.FIRSTNAME (1)
     HOCKEY.PLAYERS.LASTNAME (1)
  Boolean sieve
    Like                                        # A "Like" node
       HOCKEY.PLAYERS.PLAYERID (1)
      "b%" (varchar)
    Index scan HOCKEY.PLAYERS  (1)
      LikeBitmap index PLAYERS..PRIMARY_KEY     # Optimizer uses index to speed up LIKE
        "b%" (varchar)

In the case of MIN and MAX functions, the optimizer also tries to take advantage of indexes. In the following example we introduce the MinMaxBitmap operator.

EXPLAIN SELECT MAX(playerid) FROM players;
 Select
  List
    Max                                        # A "Max" Node
       HOCKEY.PLAYERS.PLAYERID (1)
  Index scan HOCKEY.PLAYERS  (1)
    MinMaxBitmap index PLAYERS..PRIMARY_KEY    # Optimizer uses index to quickly find MAX value
      *** null ***
Example 9: EXPLAIN command output with Indexed SubQuery Execution
This example assumes the HOCKEY schema has been restored. The index added in Example #5 has been dropped.

In this example, we want to select the number of goals per player per year for players on the Boston team that were born after 1990.

The optimizer starts with the execution of the subquery from the PLAYERS table to return only players born after 1990. It then uses that result set to perform an Index scan search of the outer SQL statement table, SCORING, based on the playerid column, which is the first column of the SCORING table’s primary key index.

The Select List shows the columns being retrieved from the SCORING table for these players that are returned by the subquery.

The output of the EXPLAIN command in this case could be misinterpreted. It shows the subquery on the PLAYERS table in two (2) places, once in the outer table Inversion operator, and once in the Boolean sieve operator. This creates the impression that the subquery is repeatedly executed. In reality, the subquery is executed once as part of the Index scan operator. The subquery in the Boolean sieve operator should be ignored.

USE HOCKEY
EXPLAIN SELECT playerid, year, goals FROM scoring
              WHERE teamid = 'BOS'
                AND playerid IN (SELECT playerid FROM players WHERE birthyear > 1990);
Select
  List
     HOCKEY.SCORING.PLAYERID (1)
     HOCKEY.SCORING.YEAR (1)
     HOCKEY.SCORING.GOALS (1)
  Boolean sieve
    And
      Eql
         HOCKEY.SCORING.TEAMID (1)
        "BOS" (varchar)
      In Select
         HOCKEY.SCORING.PLAYERID (1)
        Select
          List
             HOCKEY.PLAYERS.PLAYERID (3)
          Boolean sieve
            And
              Gtr
                 HOCKEY.PLAYERS.BIRTHYEAR (3)
                "1990" (integer)
              Eql
                 HOCKEY.SCORING.PLAYERID (1)
                 HOCKEY.PLAYERS.PLAYERID (3)
            Inndex scan HOCKEY.PLAYERS  (3)             /* execution of subquery which is to be ignored */
              Bitmap Index scan PLAYERS..PRIMARY_KEY
                 HOCKEY.SCORING.PLAYERID (1)
    Index scan HOCKEY.SCORING  (1)
      In Select Bitmap SCORING..PRIMARY_KEY
         HOCKEY.SCORING.PLAYERID (1)
        Select                                  /* execution of subquery on players table */
          List
             HOCKEY.PLAYERS.PLAYERID (2)
          Boolean sieve
            Gtr
               HOCKEY.PLAYERS.BIRTHYEAR (2)
              "1990" (integer)
            Table scan HOCKEY.PLAYERS  (2)
Example 10: EXPLAIN command output with Materialization Strategy for SubQueries
This example assumes the HOCKEY schema has been restored. The index added in Example #5 has been dropped.

The materialization strategy avoids computation of the same subquery multiple times. It is applicable only for deterministic and non-correlated subqueries, which would produce the same result for each row of the outer SQL statement table.

In the following example, we want to select the teamid and total number of goals per team for all teams in the year 2011 that were ranked 1,2, or 3 in the New England division.

This results in a full table scan on the outer table, SCORING.

Without a materialization strategy, the IN select predicate would need to be executed for each row of the SCORING table, of which there are over 45K rows, to find a match on the teams.teamid column, using the scoring.teamid column.

Using a subquery materialization strategy, the execution will compute the subquery once and store the result set in an internal, in-memory table, hashed on the column being used in the predicate, the teams.teamid column. This computation happens when retrieving the first row of the outer table, the scoring table. On retrieving each subsequent row of the scoring table, the materialized result set is searched for a match on teamid. If found, the IN select predicate evaluates to TRUE.

USE HOCKEY
EXPLAIN SELECT teamid, sum(goals) as total_goals FROM scoring
                WHERE year = 2011
                  AND teamid IN ( SELECT teamid FROM teams
                                        WHERE rank < 4 AND year = 2011 AND divisionid ='NE')
               GROUP BY teamid;

 Select
  List
     HOCKEY.SCORING.TEAMID (1)
    Alias:TOTAL_GOALS
      Sum
         HOCKEY.SCORING.GOALS (1)
  Group
    List
       HOCKEY.SCORING.TEAMID (1)
    Boolean sieve
      And
        Eql
           HOCKEY.SCORING.YEAR (1)
          "2011" (integer)
        In Select [with materialization]           /* IN SELECT predicate using the materialized in-memory result set */
           HOCKEY.SCORING.TEAMID (1)
          Select
            List
               HOCKEY.TEAMS.TEAMID (2)
            Boolean sieve
              And
                Lss
                   HOCKEY.TEAMS.RANK (2)
                  "4" (integer)
                Eql
                   HOCKEY.TEAMS.YEAR (2)
                  "2011" (integer)
                Eql
                   HOCKEY.TEAMS.DIVISIONID (2)
                  "NE" (varchar)
              Index scan HOCKEY.TEAMS  (2)             /* materialized in-memory result set */
                Bitmap Index scan TEAMS..PRIMARY_KEY
                  "2011" (integer)
                  *** null ***
      Table scan HOCKEY.SCORING  (1)               /* Full table scan of outer table */

In the previous example, the Table scan operator results in a full table scan on the outer table, SCORING. How would this EXPLAIN command output differ if an index could be used on this outer table, SCORING.

In the following example, we want to select all of the teams that scored greater than 250 points in the year 2000. The primary key column, teamid, on the outer table, TEAMS, is used to fetch all of the rows for the outer table that return true for the year=2000 predicate. The Boolean sieve operator computes the IN select predicate using the materialized in-memory subquery result set, which only needs to be executed for the rows returned by the Index scan of the outer table.

USE HOCKEY;
EXPLAIN SELECT year, teamid, rank FROM teams
          WHERE year = 2000
            AND teamid IN ( SELECT teamid FROM scoring
                                WHERE year = 2000
                               GROUP BY teamid
                               HAVING SUM(goals) > 250);

 Select
  List
     HOCKEY.TEAMS.YEAR (1)
     HOCKEY.TEAMS.TEAMID (1)
     HOCKEY.TEAMS.RANK (1)
  Boolean sieve
    And
      Eql
         HOCKEY.TEAMS.YEAR (1)
        "2000" (integer)
      In Select [with materialization]          /* IN SELECT predicate using the materialized in-memory result set */
             HOCKEY.TEAMS.TEAMID (1)
        Select
          List
             HOCKEY.SCORING.TEAMID (2)
          Group
            List
               HOCKEY.SCORING.TEAMID (2)
            Gtr
              Sum
                 HOCKEY.SCORING.GOALS (2)
              "250" (integer)
            Boolean sieve
              Eql
                 HOCKEY.SCORING.YEAR (2)
                "2000" (integer)
              Table scan HOCKEY.SCORING  (2)
    Index scan HOCKEY.TEAMS  (1)                         /* Uses Primary Key on Teams table to fetch the Outer table rows */
      Bitmap Index scan TEAMS..PRIMARY_KEY
        "2000" (integer)
        *** null ***
Example 11: EXPLAIN command output with Bitmap Index Access Ordering Property
This example assumes the HOCKEY schema has been restored. The index added in Example #5 has been dropped.

The following example illustrates the ordering property of the bitmap index access method.

The PLAYERS table has one index, the primary key index, defined on the PLAYERID column. The SQL statement is selecting multiple columns, some of which are not defined as part of the index. The optimizer will choose a bitmap access method, meaning it will quickly scan the index for all matching rows based on the predicate as defined, "PLAYERID greater than 'c' ". The rows are returned, sorted in record ID order. The rows are then ordered, based on the ORDER BY clause, the Sort operator.

SHOW TABLE players
    Found table PLAYERS in schema HOCKEY
        s:
            PLAYERID varchar(10)
                Nullable: No
            FIRSTNAME varchar(32)
            LASTNAME varchar(32)
            HEIGHT integer
            WEIGHT integer
            FIRSTNHL integer
                Nullable: No
                Default: 0
            LASTNHL integer
                Nullable: No
                Default: 0
            POSITION varchar(8)
            BIRTHYEAR integer
            BIRTHMON integer
            BIRTHDAY integer
            BIRTHCOUNTRY varchar(32)
            BIRTHSTATE varchar(32)
            BIRTHCITY varchar(32)
        Primary Index: PLAYERS..PRIMARY_KEY on field: PLAYERID

EXPLAIN SELECT playerid, firstname,lastname FROM players WHERE playerid > 'c' ORDER BY playerid;
 Select
  List
     HOCKEY.PLAYERS.PLAYERID (1)
     HOCKEY.PLAYERS.FIRSTNAME (1)
     HOCKEY.PLAYERS.LASTNAME (1)
  Sort                                              <-- Sort step caused by the ORDER BY clause
    List
      Nothing
         HOCKEY.PLAYERS.PLAYERID (1)
        *** null ***
        *** null ***
    Boolean sieve
      Gtr
         HOCKEY.PLAYERS.PLAYERID (1)
        "c" (varchar)
      Index scan HOCKEY.PLAYERS  (1)
        Bitmap Index scan PLAYERS..PRIMARY_KEY             <-- bitmap index access method
          "c" (varchar)
          *** null ***
Example 12: EXPLAIN command output with Streaming Index Access Ordering Property
This example assumes the HOCKEY schema has been restored. The index added in Example #5 has been dropped.

The streaming index fetches rows in index key order. The optimizer can make use of this ordering property to optimize the processing of GROUP BY and ORDER BY clauses of the SQL statement. If this index key order of rows, output by the streaming index, matches the order specified by the ORDER BY clause of the SQL statement, then the optimizer will completely optimize out the Sort operator from the execution plan.

As an example, the following query does not specify an ORDER BY clause. Therefore, the optimizer chooses a bitmap index access method for that query and does not sort the query result set output.

SHOW TABLE scoring
    Found table SCORING in schema HOCKEY
            PLAYERID varchar(10)
                Nullable: No
            YEAR integer
                Nullable: No
            STINT integer
                Nullable: No
            TEAMID varchar(3)
                Nullable: No
            POSITION varchar(4)
                Nullable: No
            GAMESPLAYED integer
                Nullable: No
                Default: 0
            GOALS integer
                Nullable: No
                Default: 0
            ASSISTS integer
                Nullable: No
                Default: 0
            PENALTYMINUTES integer
                Nullable: No
                Default: 0
        Primary Index: SCORING..PRIMARY_KEY on fields: PLAYERID, YEAR, STINT, TEAMID, POSITION
        Foreign Key FK_HOCKEY_SCORING_YEAR_TEAMID_#_HOCKEY_TEAMS_YEAR_TEAMID: (YEAR,TEAMID) references TEAMS(YEAR,TEAMID)
        Foreign Key FK_HOCKEY_SCORING_PLAYERID_#_HOCKEY_PLAYERS_PLAYERID: (PLAYERID) references PLAYERS(PLAYERID)

EXPLAIN SELECT * FROM scoring WHERE playerid > 'h';
 Select
  List
    HOCKEY.SCORING.PLAYERID (1)
    HOCKEY.SCORING.YEAR (1)
    HOCKEY.SCORING.STINT (1)
    HOCKEY.SCORING.TEAMID (1)
    HOCKEY.SCORING.POSITION (1)
    HOCKEY.SCORING.GAMESPLAYED (1)
    HOCKEY.SCORING.GOALS (1)
    HOCKEY.SCORING.ASSISTS (1)
    HOCKEY.SCORING.PENALTYMINUTES (1)
  Boolean sieve
    Gtr
      HOCKEY.SCORING.PLAYERID (1)
      "h" (varchar)
    Index scan HOCKEY.SCORING  (1)
      Bitmap Index scan SCORING..PRIMARY_KEY           <-- bitmap index access method
        "h" (varchar)
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***

The following example adds an ORDER BY clause to the previous example. This particular ORDER BY clause specifies a column that is included in the index. However, this column is not the first column defined for the index. Therefore, the optimizer cannot take advantage of the index to order the records. For the rows to be output in the order specified by the ORDER BY clause, a Sort operator is included in the execution plan.

EXPLAIN SELECT * FROM scoring WHERE playerid > 'h' ORDER BY teamid;
 Select
  List
     HOCKEY.SCORING.PLAYERID (1)
     HOCKEY.SCORING.YEAR (1)
     HOCKEY.SCORING.STINT (1)
     HOCKEY.SCORING.TEAMID (1)
     HOCKEY.SCORING.POSITION (1)
     HOCKEY.SCORING.GAMESPLAYED (1)
     HOCKEY.SCORING.GOALS (1)
     HOCKEY.SCORING.ASSISTS (1)
    HOCKEY.SCORING.PENALTYMINUTES (1)
  Sort                                             <-- Sort step is added by the optimizer
    List
      Nothing
         HOCKEY.SCORING.TEAMID (1)
        *** null ***
        *** null ***
    Boolean sieve
      Gtr
        HOCKEY.SCORING.PLAYERID (1)
        "h" (varchar)
      Index scan HOCKEY.SCORING  (1)
        Bitmap Index scan SCORING..PRIMARY_KEY           <-- bitmap index access method
          "h" (varchar)
          *** null ***
          *** null ***
          *** null ***
          *** null ***
          *** null ***
          *** null ***
          *** null ***
          *** null ***
          *** null ***

In the following example, we modify the ORDER BY clause to order the result set using one or more columns defined by the index. This ORDER BY column list has the columns appearing in the same order as defined by the index. In this case, the optimizer can choose a streaming index access method and can eliminate the Sort operator that was required in the previous example.

EXPLAIN SELECT * FROM scoring WHERE playerid > 'h' ORDER BY playerid, year;
 Select
  List
    HOCKEY.SCORING.PLAYERID (1)
    HOCKEY.SCORING.YEAR (1)
    HOCKEY.SCORING.STINT (1)
    HOCKEY.SCORING.TEAMID (1)
    HOCKEY.SCORING.POSITION (1)
    HOCKEY.SCORING.GAMESPLAYED (1)
    HOCKEY.SCORING.GOALS (1)
    HOCKEY.SCORING.ASSISTS (1)
    HOCKEY.SCORING.PENALTYMINUTES (1)
  Boolean sieve                                     <-- the sort step shown previously is now removed
    Gtr
      HOCKEY.SCORING.PLAYERID (1)
      "h" (varchar)
    Index scan HOCKEY.SCORING  (1)
      Streaming index SCORING..PRIMARY_KEY (PLAYERID asc, YEAR asc)  <-- streaming index access method with the sort on playerid, year
        "h" (varchar)
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
Example 13: EXPLAIN command output with Streaming Covering Index Access Ordering Property
This example assumes the HOCKEY schema has been restored. The index added in Example #5 has been dropped.

A streaming covering index is similar to a streaming index. It will also output the records in the order of the index key, which allows the optimizer to eliminate the Sort operator.

The optimizer will choose this type of index access method if the index contains all columns that are included in the SQL statement column list, where clause, group by, etc. When this is the case, the optimizer can make use of the streaming covering index access method to retrieve the column rows for the result set.

In this case, as in the previous example, the optimizer can also eliminate the Sort operator. The streaming covering index access method outputs the records in the order of the index key.

In the following example, the optimizer will use the primary key index based on the predicate, as defined by the WHERE clause, to search for all rows where "PLAYERS.PLAYERID > 'f' ". With the SQL SELECT statement column list selecting only those column(s) that are defined by the primary key index, the optimizer chooses this very efficient execution plan. This index access method also optimizes the output record ordering, therefore eliminating the Sort operator for the ORDER BY clause.

SHOW TABLE scoring
    Found table SCORING in schema HOCKEY
            PLAYERID varchar(10)
                Nullable: No
            YEAR integer
                Nullable: No
            STINT integer
                Nullable: No
            TEAMID varchar(3)
                Nullable: No
            POSITION varchar(4)
                Nullable: No
            GAMESPLAYED integer
                Nullable: No
                Default: 0
            GOALS integer
                Nullable: No
                Default: 0
            ASSISTS integer
                Nullable: No
                Default: 0
            PENALTYMINUTES integer
                Nullable: No
                Default: 0
        Primary Index: SCORING..PRIMARY_KEY on fields: PLAYERID, YEAR, STINT, TEAMID, POSITION
        Foreign Key FK_HOCKEY_SCORING_YEAR_TEAMID_#_HOCKEY_TEAMS_YEAR_TEAMID: (YEAR,TEAMID) references TEAMS(YEAR,TEAMID)
        Foreign Key FK_HOCKEY_SCORING_PLAYERID_#_HOCKEY_PLAYERS_PLAYERID: (PLAYERID) references PLAYERS(PLAYERID)

EXPLAIN SELECT playerid, year, stint, teamid, position FROM scoring WHERE playerid > 'h' ORDER BY playerid, year;
 Select
  List                                         <-- only columns included in the index are returned in the result set
    HOCKEY.SCORING.PLAYERID (1)
    HOCKEY.SCORING.YEAR (1)
    HOCKEY.SCORING.STINT (1)
    HOCKEY.SCORING.TEAMID (1)
    HOCKEY.SCORING.POSITION (1)
  Boolean sieve
    Gtr
      HOCKEY.SCORING.PLAYERID (1)
      "h" (varchar)
    Index scan HOCKEY.SCORING  (1)
      Streaming covering index SCORING..PRIMARY_KEY (PLAYERID asc, YEAR asc)   <-- streaming covering index method
        "h" (varchar)
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
        *** null ***
Example 14: EXPLAIN command output with Partition ID option
For this Example, we assume four(4) storage groups have been defined: sg1, sg2, sg3, sg4.

For tables that are partitioned, NuoDB stores the partition criteria in system tables. For the execution plan on partition tables, the optimizer can compare predicates, as defined on the WHERE clause, with this partition criteria. From this comparison, the optimizer can ignore, or "prune" entire table partitions from its search to retrieve the rows for the result set. To illustrate, we create a table that has several table partitions:

CREATE TABLE partitioned_table
 ( id INTEGER
 , description STRING
 , update_ts timestamp)
 PARTITION BY RANGE (update_ts)
  ( PARTITION p_2016_jan VALUES LESS THAN ('2016-02-01') STORE IN sg1
    PARTITION p_2016_feb VALUES LESS THAN ('2016-03-01') STORE IN sg2
    PARTITION p_2016_mar VALUES LESS THAN ('2016-04-01') STORE IN sg3
    PARTITION p_2016_apr VALUES LESS THAN ('2016-05-01') STORE IN sg4
    PARTITION p_max      VALUES LESS THAN (MAXVALUE)     STORE IN sg1);

INSERT INTO partitioned_table VALUES (1,'row1','2016-01-01');
INSERT INTO partitioned_table VALUES (2,'row2','2016-01-15');
INSERT INTO partitioned_table VALUES (3,'row3','2016-02-01');
INSERT INTO partitioned_table VALUES (4,'row4','2016-02-15');
INSERT INTO partitioned_table VALUES (5,'row5','2016-03-01');
INSERT INTO partitioned_table VALUES (6,'row6','2016-03-15');
INSERT INTO partitioned_table VALUES (7,'row7','2016-04-01');
INSERT INTO partitioned_table VALUES (8,'row8','2016-04-15');
INSERT INTO partitioned_table VALUES (9,'row9','2016-05-01');

SELECT pi.tablename,pi.partitionname,pi.partitionid
      ,pt.partitioningcolumn
      ,pc.value,pc.isdefault
  FROM system.partitionids pi, system.partitioncriteria pc
      ,system.partitionedtables pt
 WHERE pi.schema = 'TEST' AND pi.tablename = 'PARTITIONED_TABLE'
   AND pi.schema = pc.schema AND pi.tablename = pc.tablename
   AND pi.schema = pt.schema AND pi.tablename = pt.tablename
   AND pi.partitionname = pc.partitionname
 ORDER BY partitionid;
     TABLENAME     PARTITIONNAME  PARTITIONID  PARTITIONINGCOLUMN            VALUE           ISDEFAULT
 ----------------- -------------- ------------ ------------------- ------------------------- ----------

 PARTITIONED_TABLE   P_2016_JAN       132           UPDATE_TS      02/01/2016 05:00:00 +0000     0
 PARTITIONED_TABLE   P_2016_FEB       133           UPDATE_TS      03/01/2016 05:00:00 +0000     0
 PARTITIONED_TABLE   P_2016_MAR       134           UPDATE_TS      04/01/2016 04:00:00 +0000     0
 PARTITIONED_TABLE   P_2016_APR       135           UPDATE_TS      05/01/2016 04:00:00 +0000     0
 PARTITIONED_TABLE   P_MAX            136           UPDATE_TS      <null>                        1

Using predicates based on the column that the table is partitioned, which in this case is the UPATE_TS column, the optimizer can compare the predicate values in the WHERE clause to the criteria for each table partition defined. Any table partition that does not cover the predicate value range can be ignored, or "pruned" by the optimizer. The optimizer will only search those table partitions where the partition criteria falls within the predicate value range. If there are no partitions with a defined criteria that meets the SQL statement predicate value, the optimizer would search only the default table partition (the partition with ISDEFAULT value 1). This is the table partition that stores rows having partition column value that do not meet the partition criteria for any defined table partitions.

With the EXPLAIN command PARTITION_IDS option set to ON, the execution plan will display the PARTITIONID values for all partitions that the optimizer included in its search.

In the following example, the execution plan shows that the optimizer search only table partitions with PARTITIONID values of 133 and 134 to retrieve the rows for the SQL statement result set. The optimizer ignored, or "pruned", all other table partitions from its search.

EXPLAIN ( PARTITION_IDS ON ) SELECT * FROM partitioned_table WHERE update_ts BETWEEN '2016-02-01' AND '2016-03-31';
 Select
  List
     TEST.PARTITIONED_TABLE.ID (1)
     TEST.PARTITIONED_TABLE.DESCRIPTION (1)
     TEST.PARTITIONED_TABLE.UPDATE_TS (1)
  Boolean sieve
    Between
       TEST.PARTITIONED_TABLE.UPDATE_TS (1)
      "2016-02-01 00:00:00" (timestamp)
      "2016-03-31 00:00:00" (timestamp)
    Table Scan TEST.PARTITIONED_TABLE  (1)[partitionIds:133,134]

The above example shows that the optimizer performed a full table scan of two table partitions, PARTITIONID values 133 and 134. The optimizer was able to evaluate the predicate, as defined by the WHERE clause, and compare this to the partition criteria defined. All other table partitions were ignored. This is more efficient than a full table scan of the entire table, but it may not be as efficient as using an Index scan.

In the following example, if we create an index on the PARTITIONED_TABLE.ID column and include a predicate based on this ID column, the optimizer will choose the Index scan over a full search of only 1 or 2 table partitions, as it did in the previous example.

CREATE INDEX idx_partitionedtable_id ON partitioned_table(id);
EXPLAIN ( PARTITION_IDS ON ) SELECT * FROM partitioned_table
                                 WHERE update_ts BETWEEN '2016-02-01' AND '2016-03-31'
                                   AND id = 2;
 Select
  List
     TEST.PARTITIONED_TABLE.ID (1)
     TEST.PARTITIONED_TABLE.DESCRIPTION (1)
     TEST.PARTITIONED_TABLE.UPDATE_TS (1)
  Boolean sieve
    And
      Between
         TEST.PARTITIONED_TABLE.UPDATE_TS (1)
        "2016-02-01 00:00:00" (timestamp)
        "2016-03-31 00:00:00" (timestamp)
      Eql
         TEST.PARTITIONED_TABLE.ID (1)
        "2" (integer)
    Index scan TEST.PARTITIONED_TABLE  (1)
      Bitmap Index scan IDX_PARTITIONEDTABLE_ID[partitionIds:132,133,134,135,136]
        "2" (integer)