15.10 - DYNAMIC EXPLAIN - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

A query plan generated by IPE is referred to as a dynamic query plan. Dynamic query plans are not the query plans that are traditionally returned for an explained request.

IPE both provides feedback of statistical information from the execution of a plan fragment and provides feedback from the results of a plan fragment. This enables many optimizations such as partition elimination (see “Row Partition Elimination” on page 317 and “Column Partition Elimination” on page 347) and sparse join index qualification, and also enables many query rewrites such as transitive closure, predicate simplification, and unsatisfiability (see “Query Rewrite” on page 72 for more information about the various query rewrite methods).

To ask for a dynamic EXPLAIN report for a request, specify the keyword DYNAMIC preceding the keyword EXPLAIN.

The default system‑controlled setting for EXPLAIN request modifier is STATIC, so you must specify the keyword DYNAMIC preceding the keyword EXPLAIN at most sites to return a dynamic EXPLAIN report.

You can request that the report be returned in XML format by specifying the keywords IN XML following the EXPLAIN keyword.

See “EXPLAIN Request Modifier” in SQL Data Manipulation Language for more information about the EXPLAIN request modifier.

IPE produces a dynamic query plan by inserting the intermediate results from the execution of the plan fragments into the main query. A dynamic query plan processes the inserted request fragment constants as elements of the query predicates. You might not want to reveal intermediate results in the EXPLAIN report to end users who might not have the authorization to view them. For example, an end user might have SELECT access to a view that can retrieve data from many other views or tables for which the user does not have SELECT access.

If your system is set to mask intermediate dynamic query plan results by default, Teradata Database masks the intermediate result values in EXPLAIN reports by replacing them with the character string :*. See “EXPLAIN Request Modifier Phrase Terminology” on page 543 and “DYNAMIC EXPLAIN for a Masked Dynamic Query Plan” on page 634 for further information about the :* character string and its meaning.

You can generate dynamic plan EXPLAIN reports in one of the following ways.

  • Using the DYNAMIC EXPLAIN modifier.
  • Using the EXPLAIN modifier
  • Note: You can only use this method to generate a dynamic plan EXPLAIN report if the system‑determined setting for EXPLAIN mode is set to DYNAMIC.

  • Using DBQL EXPLAIN plan logging, including DBQL XMLPlan logging.
  • Note: DBQL does not log requests preceded by any form of the EXPLAIN request modifier, though it does log additional information for requests that are optimized to use a dynamic query plan, such as the number of plan fragments and the plan fragment to which the current step belongs.

    This example assumes that your system has been set to allow an unmasked dynamic plan to be displayed. For unmasked dynamic plans, the word unmasked signifies that text reporting intermediate results has not been removed from the report. In this report, the unmasked text is the values 10000 and 9999 (highlighted in boldface) in the conditions ("OPTBASE_PCTDB.t1.a1 < 10000") and ("OPTBASE_PCTDB.t2.a2 <= 9999") in step 9.

    In this case, the DYNAMIC keyword is implicit and does not need to be specified.

         DYNAMIC EXPLAIN SELECT * 
                         FROM t1, t2
                         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                                        FROM t3)
                         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    The following is the dynamic explain for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs FEEDBACK RETRIEVE step from Spool 1 (Last Use) 
         The size is estimated with high confidence to be 1 row (25 bytes).  
         The estimated time for this step is 0.01 seconds. The actual size of 
         Spool 1 is 1 row (25 bytes.) 
      8) We send an END PLAN FRAGMENT step for plan fragment 1.
      9) We do an all-AMPs JOIN step from 9999 partitions of
         OPTBASE_PCTDB.t1 by way of a RowHash match scan with a condition
         of ("OPTBASE_PCTDB.t1.a1 < 10000"), which is joined to
         OPTBASE_PCTDB.t2 by way of a RowHash match scan with a condition
         of ("OPTBASE_PCTDB.t2.a2 <= 9999").  OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
         with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The input tables OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 will not be cached in memory, but
         OPTBASE_PCTDB.t1 is eligible for synchronized scanning.  The
         result goes into Spool 1 (group_amps), which is built locally on
         the AMPs.  The size of Spool 1 is estimated with low confidence to
         be 9,999 rows (649,935 bytes).  The estimated time for this step
         is 8.24 seconds.
     10) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    This example assumes the default is to allow a masked dynamic plan to be reported. For masked dynamic plans, the word masked signifies that text reporting intermediate results has been removed from the report. This is controlled by various system‑determined settings.

    Masked values are indicated by the character string :* in the EXPLAIN text, as you can see in Step 9 of this dynamic plan. This character string replaces the masked values 10000 and 9999 seen in the previous example.

         DYNAMIC EXPLAIN SELECT * 
                         FROM t1, t2
                         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                                        FROM t3)
                         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    The following is the dynamic explain for the request.
     1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs FEEDBACK RETRIEVE step from Spool 1 (Last Use)
         The size is estimated with high confidence to be 1 row (25 bytes).  
         The estimated time for this step is 0.01 seconds. The actual size
         of Spool 1 is 1 row (25 bytes). 
      8) We send an END PLAN FRAGMENT step for plan fragment 1.
      9) We do an all-AMPs JOIN step from 9999 partitions of
         OPTBASE_PCTDB.t1 by way of a RowHash match scan with a condition
         of ("OPTBASE_PCTDB.t1.a1 < :*"), which is joined to
         OPTBASE_PCTDB.t2 by way of a RowHash match scan with a condition
         of ("OPTBASE_PCTDB.t2.a2 <= :*").  OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
         with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The input tables OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 will not be cached in memory, but
         OPTBASE_PCTDB.t1 is eligible for synchronized scanning.  The
         result goes into Spool 1 (group_amps), which is built locally on
         the AMPs.  The size of Spool 1 is estimated with low confidence to
         be 9,999 rows (649,935 bytes).  The estimated time for this step
         is 8.24 seconds.
     10) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    This example demonstrates a DYNAMIC EXPLAIN report when the system‑controlled setting for displaying dynamic plans has been set not to allow dynamic plans to be reported. In this case, Teradata Database reports the static plan for the request.

         DYNAMIC EXPLAIN SELECT * 
                         FROM t1, t2
                         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                                        FROM t3)
                         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE).  The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the Dispatcher. 
         The size is estimated with high confidence to be 1 row (25 bytes).  
         The estimated time for this step is 0.01 seconds. 
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t1 by way of a
         RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20"), which is joined to OPTBASE_PCTDB.t2 by way of a RowHash
         match scan with no residual conditions.  OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
         with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The input tables OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 will not be cached in memory, but
         OPTBASE_PCTDB.t1 is eligible for synchronized scanning.  The
         result goes into Spool 5 (group_amps), which is built locally on
         the AMPs.  The size of Spool 5 is estimated with no confidence to
         be 33,333,334 rows (2,166,666,710 bytes).  The estimated time for
         this step is 1 minute and 10 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    The explained request for this example is not eligible for IPE because it is preceded by a USING request modifier. As a result, the DYNAMIC EXPLAIN request returns the static plan for the request.

         DYNAMIC EXPLAIN 
         USING (x INTEGER)
         SELECT * 
         FROM t1, t2
         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                        FROM t3)
         AND t1.a1 = t2.a2 
         AND t1.a1 = :x;
     
         *** Help information returned. 35 rows.
         *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE) but dynamic plan does not support USING request modifier.  The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock OPTBASE_PCTDB.t3 for read.
      3) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.05 seconds.
      4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.02 seconds.
      5) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher.  The size is estimated with high confidence to be 1
         row.  The estimated time for this step is 0.02 seconds.
      6) We do a single-AMP JOIN step from OPTBASE_PCTDB.t2 by way of the
         unique primary index "OPTBASE_PCTDB.t2.a2 = :x", which is joined
         to OPTBASE_PCTDB.t1 by way of the unique primary index
         "OPTBASE_PCTDB.t1.a1 = :x" with a residual condition of (
         "(OPTBASE_PCTDB.t1.a1 < :%SSQ21) AND (OPTBASE_PCTDB.t1.a1 = :x)").
         OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using a merge
         join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The result goes into Spool 5 (one-amp),
         which is built locally on that AMP.  The size of Spool 5 is
         estimated with high confidence to be 1 row (87 bytes).  The
         estimated time for this step is 0.02 seconds.
      7) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.11 seconds.

    The explained request for this example is not eligible for IPE because it specifies no single‑row access or scalar subquery operations. The static plan for the request is displayed.

    There are no changes in the EXPLAIN text report from would be reported if IPE were disabled.

         DYNAMIC EXPLAIN SELECT * 
                         FROM t1 
                         WHERE a3 > 10;
     
     *** Help information returned. 12 rows.
     *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      2) Next, we lock PLS.t1 for read.
      3) We do an all-AMPs RETRIEVE step from OPTBASE_PCTDB.t1 by way of an
         all-rows scan with a condition of ("OPTBASE_PCTDB.t1.a3 > 10") into
         Spool 1 (all_amps), which is built locally on the AMPs.  The size of
         Spool 1 is estimated with no confidence to be 1 row (54 bytes).  The
         estimated time for this step is 0.03 seconds.
      4) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.03 seconds.

    This example demonstrates an EXPLAIN report where the request being explained is eligible for IPE, but does not meet certain system‑determined thresholds for execution costs.

    Because system‑determined thresholds for IPE are not met, Teradata Database displays the static plan for the request instead of the dynamic plan.

         DYNAMIC EXPLAIN SELECT * 
                         FROM t1, t2
                         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                                        FROM t3)
                         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE) but does not meet thresholds.  The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher. The size is estimated with high confidence to be 1
         row. The estimated time for this step is 0.02 seconds.
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
         RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
         a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20").  OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
         a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The result goes into Spool 5 (group_amps),
         which is built locally on the AMPs.  The size of Spool 5 is
         estimated with no confidence to be 4 rows (260 bytes).  The
         estimated time for this step is 0.02 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    When you submit a DYNAMIC EXPLAIN for a request that is eligible for IPE, but IPE is disabled for your system, Teradata Database displays the static plan for the request. The first sentences of EXPLAIN text, highlighted in boldface type in the example, explain that the request is eligible for IPE, but IPE is disabled.

         DYNAMIC EXPLAIN 
         SELECT * 
         FROM t1, t2
         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                        FROM t3)
         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE). IPE is disabled. The following is the static plan for the request.  
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher. The size is estimated with high confidence to be 1
         row. The estimated time for this step is 0.02 seconds.
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
         RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
         a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20").  OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
         a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The result goes into Spool 5 (group_amps),
         which is built locally on the AMPs.  The size of Spool 5 is
         estimated with no confidence to be 4 rows (260 bytes).  The
         estimated time for this step is 0.02 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    When you submit a STATIC EXPLAIN for a request that is eligible for IPE, the reported text is identical to that returned if you had just submitted EXPLAIN except for the additional sentences highlighted in boldface before the first step.

         STATIC EXPLAIN 
         SELECT * 
         FROM t1, t2
         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                        FROM t3)
         AND   t1.a1 = t2.a2;
     
         *** Help information returned. 36 rows.
         *** Total elapsed time was 1 second.
     
    Explanation
    --------------------------------------------------------------------------- 
    This request is eligible for incremental planning and execution (IPE).  The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher. The size is estimated with high confidence to be 1
         row. The estimated time for this step is 0.02 seconds.
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t1 by way of a
         RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20"), which is joined to OPTBASE_PCTDB.t2 by way of a RowHash
         match scan with no residual conditions.  OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 are joined using a sliding-window merge join,
         with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The input tables OPTBASE_PCTDB.t1 and
         OPTBASE_PCTDB.t2 will not be cached in memory, but
         OPTBASE_PCTDB.t1 is eligible for synchronized scanning.  The
         result goes into Spool 5 (group_amps), which is built locally on
         the AMPs.  The size of Spool 5 is estimated with no confidence to
         be 33,333,334 rows (2,166,666,710 bytes).  The estimated time for
         this step is 1 minute and 10 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    When you submit a STATIC EXPLAIN for a request that is eligible for IPE but does not meet any specified thresholds, the reported text is identical to that returned if you had just submitted EXPLAIN except for the additional sentences highlighted in boldface before the first step.

         STATIC EXPLAIN 
         SELECT * 
         FROM t1, t2
         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                        FROM t3)
         AND   t1.a1 = t2.a2;
     
         *** Help information returned. 36 rows.
         *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE) but doesn't meet thresholds.  The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher. The size is estimated with high confidence to be 1
         row. The estimated time for this step is 0.02 seconds.
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
         RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
         a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20").  OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
         a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The result goes into Spool 5 (group_amps),
         which is built locally on the AMPs.  The size of Spool 5 is
         estimated with no confidence to be 4 rows (260 bytes).  The
         estimated time for this step is 0.02 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    When you submit a STATIC EXPLAIN for a request that is eligible for IPE but IPE is disabled, the reported text is identical to that returned if you had just submitted EXPLAIN except for the additional sentences highlighted in boldface before the first step.

         STATIC EXPLAIN 
         SELECT * 
         FROM t1, t2
         WHERE t1.a1 < (SELECT MAX(t3.a3) 
                        FROM t3)
         AND   t1.a1 = t2.a2;
     
    *** Help information returned. 36 rows.
    *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
    This request is eligible for incremental planning and execution (IPE). IPE is disabled. The following is the static plan for the request.
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t3.
      2) Next, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on
         a RowHash to prevent global deadlock for OPTBASE_PCTDB.t2.
      3) We lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      4) We lock OPTBASE_PCTDB.t3 for read, we lock OPTBASE_PCTDB.t2 for
         read, and we lock OPTBASE_PCTDB.t1 for read.
      5) We do an all-AMPs SUM step to aggregate from OPTBASE_PCTDB.t3 by
         way of an all-rows scan with no residual conditions.  Aggregate
         Intermediate Results are computed globally, then placed in Spool 3.
         The size of Spool 3 is estimated with high confidence to be 1 row
         (19 bytes).  The estimated time for this step is 0.02 seconds.
      6) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
         an all-rows scan into Spool 1 (all_amps), which is built locally
         on the AMPs.  The size of Spool 1 is estimated with high
         confidence to be 1 row (25 bytes).  The estimated time for this
         step is 0.00 seconds.
      7) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
         by way of an all-rows scan and send the rows back to the
         Dispatcher.
      8) We do an all-AMPs JOIN step from OPTBASE_PCTDB.t2 by way of a
         RowHash match scan, which is joined to OPTBASE_PCTDB.t1 by way of
         a RowHash match scan with a condition of ("OPTBASE_PCTDB.t1.a1 <
         :%SSQ20").  OPTBASE_PCTDB.t2 and OPTBASE_PCTDB.t1 are joined using
         a merge join, with a join condition of ("OPTBASE_PCTDB.t1.a1 =
         OPTBASE_PCTDB.t2.a2").  The result goes into Spool 5 (group_amps),
         which is built locally on the AMPs.  The size of Spool 5 is
         estimated with no confidence to be 4 rows (260 bytes).  The
         estimated time for this step is 0.02 seconds.
      9) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 5 are sent back to the user as the result of
         statement 1.

    When you submit a STATIC EXPLAIN for a request that is not eligible for IPE, the reported EXPLAIN text is the same as would be generated if you had not specified STATIC.

         STATIC EXPLAIN
         SELECT * 
         FROM t1 
         WHERE a3 > 10;
     
          *** Help information returned. 12 rows.
          *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock a distinct OPTBASE_PCTDB."pseudo table" for read on a
         RowHash to prevent global deadlock for OPTBASE_PCTDB.t1.
      2) Next, we lock PLS.t1 for read.
      3) We do an all-AMPs RETRIEVE step from OPTBASE_PCTDB.t1 by way of an
         all-rows scan with a condition of ("OPTBASE_PCTDB.t1.a3 > 10") into
         Spool 1 (all_amps), which is built locally on the AMPs.  The size of
         Spool 1 is estimated with no confidence to be 1 row (54 bytes).  The
         estimated time for this step is 0.03 seconds.
      4) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> The contents of Spool 1 are sent back to the user as the result of
         statement 1.  The total estimated time is 0.03 seconds.