STATIC EXPLAIN | Interpreting EXPLAIN Output | Teradata Vantage - STATIC EXPLAIN - Analytics Database - Teradata Vantage

STATIC EXPLAIN Report When the Request Is Eligible for Incremental Planning and Execution

This example assumes that your system has been set never to report a dynamic plan. When this is the case, Vantage the static plan with an indication that the request is eligible for IPE. In this case, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

STATIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

This shows a portion of the EXPLAIN output:

Explanation
------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE). The following is the static plan for the request. 
...
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.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.
...

STATIC EXPLAIN Where the Request Is Eligible for Incremental Planning and Execution But Does Not Meet Thresholds

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.

The example assumes that your system has been set never to report a dynamic plan. When this is the case, Vantage reports the static plan with an indication that the request is eligible for IPE. In this case, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

STATIC EXPLAIN 
SELECT *
FROM t1, t2
WHERE t1.a1 < (SELECT MAX(t3.a3)
               FROM t3)
AND   t1.a1 = t2.a2;

This shows a portion of the EXPLAIN output:

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

STATIC EXPLAIN Where the Request is Eligible for IPE But IPE is Disabled

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 that appear 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;

This shows a portion of the EXPLAIN output:

Explanation
-----------------------------------------------------------------------
This request is eligible for incremental planning and execution (IPE). IPE is disabled. The following is the static plan for the request. 
...
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.
...

STATIC EXPLAIN Where the Request Is Not Eligible for Incremental Planning and Execution

This example demonstrates an EXPLAIN report where the request being explained is not eligible for IPE.

The example assumes that your system has been set never to report a dynamic plan. For this example, the request is not eligible for IPE, so Vantage does not report anything about the eligibility of the request for IPE. For this example, the STATIC keyword is implicit and does not need to be specified as part of the EXPLAIN request modifier.

STATIC EXPLAIN 
SELECT *
FROM t1
WHERE a3 > 10;

Explanation
-----------------------------------------------------------------
...
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.
...