15.10 - EXPLAIN Request Modifier and Column-Partition Access - 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

EXPLAIN reports indicate column partition accesses, deletions, joins, and column partition eliminations performed during query optimization.

The following set of CREATE TABLE requests defines the tables used in the examples for this topic. All of the tables are created in database PLS.

You should assume that 20 column partition contexts are available for each of the examples presented.

     CREATE TABLE t1 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER, 
       d INTEGER, 
       e INTEGER, 
       f INTEGER, 
       g INTEGER, 
       h INTEGER, 
       i INTEGER, 
       j INTEGER,
       k INTEGER, 
       l INTEGER, 
       m INTEGER, 
       n INTEGER, 
       o INTEGER, 
       p INTEGER, 
       q INTEGER, 
       r INTEGER, 
       s INTEGER, 
       t INTEGER,
       u INTEGER, 
       v INTEGER, 
       w INTEGER, 
       x INTEGER,
       y INTEGER, 
       z INTEGER)
     PARTITION BY COLUMN;
 
     CREATE TABLE t2 AS t1 
     WITH NO DATA 
     PRIMARY INDEX (a,b);
 
     CREATE TABLE t3 AS t1 
     WITH NO DATA;
 
     CREATE TABLE t4 AS t1 
     WITH NO DATA 
     NO PRIMARY INDEX
     PARTITION BY (COLUMN, 
                   RANGE_N(b BETWEEN 1 
                             AND    10 
                             EACH    1);
 
     CREATE TABLE t5 (
       a INTEGER,
       b INTEGER,
       c INTEGER,
       d INTEGER,
       e INTEGER,
       f INTEGER,
       g INTEGER,
       h INTEGER,
       i INTEGER,
       j INTEGER)
     PARTITION BY COLUMN;
 
     CREATE TABLE t6 AS t5
     WITH NO DATA
     PRIMARY INDEX (a,b);

This example shows EXPLAIN text for selecting 4 of the columns from the column‑partitioned table PLS.t1.

The 5 column partitions reported by the EXPLAIN text includes the 4 column partitions specified in the select list of the SELECT request plus the delete column partition.

     EXPLAIN SELECT a, b, g, p 
             FROM t1;
 
 *** Help information returned. 12 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock PLS.t1 for read on a reserved RowHash to prevent 
     global deadlock.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 5 column partitions of PLS.t1 by
     way of an all-rows scan with no residual conditions into Spool 1
     (all_amps), which is built locally on the AMPs.  The size of Spool 1 
     is estimated with low confidence to be 2 rows (614 bytes).  The
     estimated time for this step is 0.01 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.01 seconds.

New terminology in this explanation is defined as follows:

 

                 Phrase

                                                                    Definition

from 5 column partitions of

The 4 requested columns plus the delete column can be accessed from the reported column partitions.

This example shows EXPLAIN text for selecting 19 of the 26 columns from the column‑partitioned table PLS.t1.

The count of 20 for the number of column partitions includes the 19 column partitions specified in the select list of the SELECT request plus the delete column partition.

     EXPLAIN SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t
             FROM t1;
 
 *** Help information returned. 12 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 20 column partitions of PLS.t1 by
     way of an all-rows scan with no residual conditions into Spool 1
     (all_amps), which is built locally on the AMPs.  The size of Spool 1 
     is estimated with low confidence to be 2 rows (614 bytes).  The
     estimated time for this step is 0.01 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.01 seconds.

Example 2 does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for selecting 20 of the 26 columns from the column‑partitioned table PLS.t1. In this example, there are not enough contexts available to process the request optimally. (For this example, there are 20 contexts available; for other systems, the number of available contexts is usually higher.)

     EXPLAIN SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u
             FROM t1;
 
 *** Help information returned. 13 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 21 column partitions (20 
     contexts) of PLS.t1 using covering CP merge Spool 2 (2 subrow 
     partitions and Last Use) by way of an all-rows scan with no residual
     conditions into Spool 1 (all_amps), which is built locally on the
     AMPs.  The size of Spool 1 is estimated with low confidence to be
     2 rows (614 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.

The retrieved 21 column partitions in step 3 includes the 20 selected partitions specified in the select list of the SELECT request and the delete column partition from the column‑partitioned table.

21 exceeds the 20 available column partition contexts, so Teradata Database merges 20 column partitions from the column‑partitioned table PLS.t1, including the delete column partition, into the first subrow column partition of the CP merge spool. The remaining column partition that needs to be accessed from the column‑partitioned table is copied to the second subrow column partition in the CP merge spool for a total of 2 subrow column partitions. This reduces the number of column partitions to be accessed at one time, which is limited by the number of available column partition contexts. The result is then retrieved from the 2 subrow column partitions of the CP merge spool.

New terminology in this explanation is defined as follows:

 

                          Phrase

                                                                 Definition

from 21 column partitions (20 contexts) of PLS.t1 using covering CP merge Spool 2 (2 subrow partitions and Last Use)

Up to 21 column partitions of PLS.t1 might need to be accessed using 20 column partition contexts. A CP merge spool is created and used to merge some column partitions from PLS.t1, then the resulting 2 subrow column partitions are read from the CP merge spool.

The number of merges needed depends on the number of column partitions that need to be accessed and the number of available column partition contexts as indicated in the preceding from 21 column partitions (20 contexts) phrase.

This example shows EXPLAIN text for selecting all of the columns from the column‑partitioned table PLS.t1.

     EXPLAIN SELECT * 
             FROM t1;
 
      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 27 column partitions (20 
     contexts) of PLS.t1 using covering CP merge Spool 2 (2 subrow 
     partitions and Last Use) by way of an all-rows scan with no residual
     conditions into Spool 1 (all_amps), which is built locally on the
     AMPs.  The size of Spool 1 is estimated with low confidence to be
     2 rows (614 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.

Example 4 does not introduce any new EXPLAIN text terminology.

This example shows example EXPLAIN text for selecting all of the columns from the multilevel column‑partitioned and row‑partitioned table PLS.t4.

This is similar to the previous example except only two 2 partitions of the mixed column‑ and row‑partitioned table need to be read. Because there are 27 column partitions to be accessed and 2 row partitions, there are 52 combined partitions to be accessed: 2 row partitions from each of the 27 column partitions.

     EXPLAIN SELECT * 
             FROM t4 
             WHERE b BETWEEN 4 AND 5;
 
      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t4.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 52 combined partitions (27 
     column partitions and 20 contexts) of PLS.t4 using covering CP 
     merge Spool 2 (2 subrow partitions and Last Use) with a condition of
     ("(PLS.t4.b <= 5) AND (PLS.t4.b >= 4)") 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 (307 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.

New terminology in this explanation is defined as follows:

 

                         Phrase

                                                             Definition

from 52 combined partitions (27 column partitions and 20 contexts) of PLS.t4 using covering CP merge Spool 2 (2 subrow partitions and Last Use)

This phrase indicates that the rows and columns for up to 52 combined partitions of table t4 might need to be accessed. For the column-partitioning level, 27 column partitions may need to be accessed.

For this phrase to occur, the column‑partitioned table or join index would have both row and column partitioning, as t4 does

For the column-partitioning level, 20 column partition contexts are used to access up to 27 column partitions.

52 combined partitions and 27 column partitions are both greater than 2, as is required for this phrase to be reported.

20 contexts is greater than 2 and less than 27-1, or 26, as is required for this phrase to be reported.

One of the column partitions that might be accessed is the delete column partition. Not all of the m1 column partitions might need to be accessed if no rows qualify. The phrase using covering CP merge Spool follows the table name, which is t4.

This example shows EXPLAIN text for selecting 20 of the 26 columns from the multilevel column‑partitioned and row‑partitioned table PLS.t4.

     EXPLAIN SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t,u
             FROM t4 
             WHERE b BETWEEN 4 AND 5;
 
 *** Help information returned. 14 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t4.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 42 combined partitions (21 
     column partitions and 20 contexts) of PLS.t4 using covering CP 
     merge Spool 2 (2 subrow partitions and Last Use) with a condition of
     ("(PLS.t4.b <= 5) AND (PLS.t4.b >= 4)") 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 (307 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.

Example 6 does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for selecting 19 of the 26 columns from the multilevel column‑partitioned and row‑partitioned table PLS.t4.

     EXPLAIN SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,r,s,t
     FROM t4 
     WHERE b BETWEEN 4 AND 5;
 
 *** Help information returned. 13 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t4.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 40 combined partitions (20 
     column partitions) of PLS.t4 with a condition of ("(PLS.t4.b <= 5)
     AND (PLS.t4.b >= 4)") 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 (307 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.

Example 7 does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for selecting 2 columns from the column‑partitioned table PLS.t1 with a WHERE clause condition specified on each of the 26 columns from the table.

     EXPLAIN SELECT a, q 
             FROM t1 
             WHERE a>1 
             AND   b<=1000 
             AND   c=10 
             AND   d=11 
             AND   e<40 
             AND   f<>87 
             AND   g=92 
             AND   h>=101 
             AND   i=3000 
             AND   j>=5
             AND   k=12 
             AND   l=0 
             AND   m=0 
             AND   n IS NOT NULL 
             AND   o=1
             AND   p=-1 
             AND   q=1 
             AND   r<10 
             AND   s=9 
             AND   t>33 
             AND   u=0
             AND   v=0 
             AND   w=0 
             AND   x>101 
             AND   y=0 
             AND   z=0;
 
 *** Help information returned. 20 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 2 column partitions of PLS.t1 using
     rowid Spool 2 (Last Use) built from 27 column partitions (20 contexts
     and Last use) with a condition of ("(PLS.t1.a > 1) AND (PLS.t1.b <=
     1000) AND (PLS.t1.c = 10) AND (PLS.t1.d = 11) AND (PLS.t1.e < 40)
     AND (PLS.t1.f <> 87) AND (PLS.t1.g = 92) AND (PLS.t1.h >= 101) AND
     (PLS.t1.i = 3000) AND (PLS.t1.j >= 5) AND (PLS.t1.k = 12) AND (PLS.t1.l
     = 0) AND (PLS.t1.m = 0) AND (PLS.t1.n IS NOT NULL) AND (PLS.t1.o = 1)
     AND (PLS.t1.p = -1) AND (PLS.t1.q = 1) AND (PLS.t1.r < 10) AND
     (PLS.t1.s = 9) AND (PLS.t1.t > 33) AND (PLS.t1.u = 0) AND (PLS.t1.v =
      0) AND (PLS.t1.w = 0) AND (PLS.t1.x > 101) AND (PLS.t1.y = 0) AND
     (PLS.t1.z = 0)") 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
     (307 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.

New terminology in this explanation is defined as follows:

 

                          Phrase

                                                           Definition

from 2 partitions of PLS.t1 using rowid Spool 2 (Last Use) built from 27 column partitions (20 contexts and Last use)

Up to 2 column partitions of PLS.t1 might need to be accessed. There are column partition contexts available for each of the 2 column partitions.

A rowID spool is created that contains the rowids of rows in the table or join index that qualify and then the column‑partitioned table or join index is read driven by this rowid spool. This is the last usage of this rowid spool so it can be deleted. k is the spool number for the rowid spool

This example shows EXPLAIN text for selecting 21 of the 26 columns from a column‑partitioned table with a predicate specified for each of those columns in the WHERE clause.

     EXPLAIN SELECT a,b,c,d,e,f,g,h,i,j,k,l,m,o,p,q,r,s,t,u,v
             FROM t1 
             WHERE a>1 
             AND b<=1000 
             AND c=10 
             AND d=11 
             AND e<40 
             AND f<>87 
             AND g=92 
             AND h>=101 
             AND i=3000 
             AND j>=5
             AND k=12 
             AND l=0 
             AND m=0 
             AND n IS NOT NULL 
             AND o=1
             AND p=-1 
             AND q=1 
             AND r<10 
             AND s=9 
             AND t>33 
             AND u=0
             AND v=0 
             AND w=0 
             AND x>101 
             AND y=0 
             AND z=0;
 
 *** Help information returned. 21 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock PLS.t1 for read.
  3) We do an all-AMPs RETRIEVE step from 21 column partitions of PLS.t1 using
     covering CP merge Spool 3 (20 contexts and Last Use) and rowid Spool 2
     (Last Use) built from 27 column partitions (20 contexts and Last use)
     with a condition of ("(PLS.t1.a > 1) AND (PLS.t1.b <= 1000) AND
     (PLS.t1.c = 10) AND (PLS.t1.d = 11) AND (PLS.t1.e < 40) AND (PLS.t1.f
     <> 87) AND (PLS.t1.g = 92) AND (PLS.t1.h >= 101) AND (PLS.t1.i = 3000)
     AND (PLS.t1.j >= 5) AND (PLS.t1.k = 12) AND (PLS.t1.l = 0) AND
     (PLS.t1.m = 0) AND (PLS.t1.n IS NOT NULL) AND (PLS.t1.o = 1) AND
     (PLS.t1.p = -1) AND (PLS.t1.q = 1) AND (PLS.t1.r < 10) AND (PLS.t1.s
     = 9) AND (PLS.t1.t > 33) AND (PLS.t1.u = 0) AND (PLS.t1.v = 0) AND
     (PLS.t1.w = 0) AND (PLS.t1.x > 101) AND (PLS.t1.y = 0) AND (PLS.t1.z
     = 0)") 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
     (307 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.

New terminology in this explanation is defined as follows:

 

                          Phrase

                                                            Definition

from 21 column partitions of PLS.t1 using covering CP merge Spool 3 (20 contexts and Last Use) and rowid Spool 2 (Last Use) built from 27 column partitions (20 contexts and Last use)

Up to 21 column partitions of PLS.t1 might need to be accessed. There are column partition contexts available for each of the 2 column partitions.

A column‑partitioned merge spool is created and used to merge column partitions from PLS.t1 and then the resulting column partitions are read from the column‑partitioned merge spool.

A rowID spool is created that contains the rowIDs of rows in PLS.t1 that qualify and then the column‑partitioned table is read driven by this rowID spool. This is the last usage of this rowID spool so it can be deleted.

This example shows EXPLAIN text for inserting all of the columns from the nonpartitioned, primary‑indexed table PLS.t6 into the column‑partitioned table PLS.t5 using the default locking for PLS.t6.

     EXPLAIN INSERT INTO t5 
               SELECT * 
               FROM t6;
 
      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t6.
  2) Next, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t5.
  3) We lock PLS.t6 for read, and we lock PLS.t5 for write.
  4) We do an all-AMPs MERGE into 10 column partitions of     
     PLS.t5 from PLS.t6.  The size is estimated with no confidence to be
     2 rows.  The estimated time for this step is 0.71 seconds.
  5) We spoil the parser's dictionary cache for the table.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

The 10 column partitions are the default user-specified column partitions from PLS.t6, whose definition is copied from PLS.t5, but is neither column‑partitioned nor row‑partitioned and has a primary index defined on columns a and b. The delete column partition is not affected.

New terminology in this explanation is defined as follows:

 

                Phrase

                                        Definition

MERGE into 10 column partitions of

The rows and columns of table PLS.t6 (which has column partitioning in this case) for up to 10 combined partitions can be accessed.

This example shows EXPLAIN text for using an INSERT … SELECT request to insert all of the columns from the non‑column‑partitioned table PLS.t6 into the column‑partitioned table PLS.t5 while using a LOCKING request modifier to lock PLS.t6 for ACCESS.

     EXPLAIN LOCKING t6 FOR ACCESS 
             INSERT INTO t5 
               SELECT * 
               FROM t6;
 
      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t5.
  2) Next, we lock a distinct PLS."pseudo table" for access on a RowHash
     to prevent global deadlock for PLS.t6.
  3) We lock PLS.t5 for write, and we lock PLS.t6 for access.
  4) We do an all-AMPs MERGE into 10 column partitions of     
     PLS.t5 from PLS.t6.  The size is estimated with no confidence to be
     2 rows.  The estimated time for this step is 0.71 seconds.
  5) We spoil the parser's dictionary cache for the table.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 0.72 seconds.

This example does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for an INSERT … SELECT request that inserts all of the columns from the primary‑indexed, nonpartitioned table PLS.t2 into the column‑partitioned table PLS.t1.

     EXPLAIN INSERT INTO t1 
               SELECT *
               FROM t2;
 
      *** Help information returned. 12 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t2.
  2) Next, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t1.
  3) We lock PLS.t2 for read, and we lock PLS.t1 for write.
  4) We do an all-AMPs MERGE into 26 column partitions (20 contexts) of
     PLS.t1 from PLS.t2.  The size is estimated with no confidence to be
     2 rows.  The estimated time for this step is 0.71 seconds.
  5) We spoil the parser's dictionary cache for the table.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

New terminology in this explanation is defined as follows.:

 

                       Phrase

                                                             Definition

MERGE into 26 column partitions (20 contexts) of

The columns of table PLS.t1 (which has column partitioning in this case) can be accessed.

20 contexts are used to access the 26 combined partitions. Because the available 20 contexts are not enough to accommodate 26 combined partitions, there must be 2 scans of source table t2.

1 Merge 20 columns into t1.

2 Merge the remaining 6 columns into t1.

Because there is a READ lock on t2, the rows read in each scan are the same.

This example is similar to the previous example except that it specifies a LOCKING request modifier on the source table. The example shows EXPLAIN text for inserting all of the columns from the primary‑indexed, nonpartitioned table PLS.t2 into the column‑partitioned table PLS.t1. In this case, the source table PLS.t2 must be spooled before its rows can be inserted into the target column‑partitioned table, PLS.t1.

Also note that if table t2 had been read twice as was done in “INSERT … SELECT From a Nonpartitioned Source Table With a Primary Index Into a Column‑Partitioned Target Table” on page 607, the rows could change from the first scan to the second and corrupt table t1.

     EXPLAIN LOCKING t2 FOR ACCESS 
             INSERT INTO t1 
               SELECT * 
               FROM t2;
 
 *** Help information returned. 19 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock a distinct PLS."pseudo table" for access on a RowHash
     to prevent global deadlock for PLS.t2.
  3) We lock PLS.t1 for write, and we lock PLS.t2 for access.
  4) We do an all-AMPs RETRIEVE step from PLS.t2 by way of an all-rows
     scan with no residual conditions into Spool 1 (all_amps), which is
     spooled locally on the AMPs.  The size of Spool 1 is estimated with
     low confidence to be 2 rows (614 bytes).  The estimated time for this
     step is 0.01 seconds.
  5) We do an all-AMPs MERGE into 26 column partitions (20 contexts) of
     PLS.t1 from Spool 1 (Last Use).  The size is estimated with low
     confidence to be 2 rows.  The estimated time for this step is 0.71
     seconds.
  6) We spoil the parser's dictionary cache for the table.
  7) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 0.72 seconds.

This example does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for inserting all of the columns from the column‑partitioned table PLS.t3 into the column‑partitioned table PLS.t1.

     EXPLAIN INSERT INTO t1 
               SELECT * 
               FROM t3;
 
 *** Help information returned. 18 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t1.
  2) Next, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t3.
  3) We lock PLS.t1 for write, and we lock PLS.t3 for read.
  4) We do an all-AMPs RETRIEVE step from 27 column partitions (20 contexts)
     of PLS.t1 using CP merge Spool 2 by way of an all-rows scan with no
     residual conditions into Spool 1 (all_amps), which is spooled locally
     on the AMPs.  The size of Spool 1 is estimated with low confidence to
     be 2 rows (614 bytes).  The estimated time for this step is 0.01 seconds.
  5) We do an all-AMPs MERGE into 26 column partitions (20 contexts) of
     PLS.t1 from Spool 1 (Last Use).  The size is estimated with no
     confidence to be 2 rows.  The estimated time for this step is 0.71
     seconds.
  5) We spoil the parser's dictionary cache for the table.
  6) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

This example does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for inserting all of the columns from the primary‑indexed, nonpartitioned table PLS.t2 into the multilevel row‑ and column‑partitioned table PLS.t4.

     EXPLAIN INSERT INTO t4 
               SELECT * 
               FROM t2;
 
 *** Help information returned. 20 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we lock a distinct PLS."pseudo table" for read on a RowHash
     to prevent global deadlock for PLS.t2.
  2) Next, we lock a distinct PLS."pseudo table" for write on a RowHash
     to prevent global deadlock for PLS.t4.
  3) We lock PLS.t2 for read, and we lock PLS.t4 for write.
  4) We do an all-AMPs RETRIEVE step from 27 column partitions (20
     contexts) of PLS.t1 using CP merge Spool 2 by way of an all-rows scan
     with no residual conditions into Spool 1 (all_amps), which is spooled
     locally on the AMPs.  The size of Spool 1 is estimated with low
     confidence to be 2 rows (614 bytes).  The estimated time for this step
     is 0.01 seconds.
  5) We do an all-AMPs MERGE into 26 column partitions (20 contexts) of
     PLS.t4 from Spool 1 (Last Use).  The size is estimated with low
     confidence to be 2 rows.  The estimated time for this step is 0.71
     seconds.
  6) We spoil the parser's dictionary cache for the table.
  7) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 0.72 seconds.

This example does not introduce any new EXPLAIN text terminology.

This example shows EXPLAIN text for a single‑row insert with values into the column‑partitioned table PLS.t1. Because multiple column partition contexts are not needed for this INSERT request, the EXPLAIN text only specifies the number of column partitions.

     EXPLAIN INSERT INTO t1 
             VALUES (1, 2,,,,,,,,,,,,,,,,,,,,,,,,);
 
 *** Help information returned. 4 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we do an INSERT into 26 column partitions of PLS.t1.  The
     estimated time for this step is 0.03 seconds.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 0.03 seconds.

New terminology in this explanation is defined as follows:

 

                               Phrase

                                                        Definition

INSERT into 26 column partitions of PLS.t1

One row is inserted into PLS.t1 with 26 column partitions.

This example shows EXPLAIN text for a single‑row insert with values taken from a USING request modifier. Apart from the data being inserted into PLS.t1 coming from a USING request modifier via host variables or parameters in the VALUES clause rather than being explicitly specified in the VALUES clause, the EXPLAIN text and explanation for this example are identical to the EXPLAIN text and explanation for the previous example.

     EXPLAIN USING (a INTEGER, b INTEGER, c INTEGER, d INTEGER, 
                    e INTEGER, f INTEGER, g INTEGER, h INTEGER, 
                    i INTEGER, j INTEGER, k INTEGER, l INTEGER, 
                    m INTEGER, n INTEGER, o INTEGER, p INTEGER, 
                    q INTEGER, r INTEGER, s INTEGER, t INTEGER, 
                    u INTEGER, v INTEGER, w INTEGER, x INTEGER, 
                    y INTEGER, z INTEGER)
             INSERT INTO t1 VALUES (:a, :b, :c, :d, :e, :f, :g, :h, :i,
                                    :j, :k, :l, :m, :n, :o, :p, :q, :r,
                                    :s, :t, :u, :v, :w, :x, :y, :z);
 
 *** Help information returned. 4 rows.
 *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we do an INSERT into 26 column partitions of
     PLS.t1.  The estimated time for this step is 1.72 seconds.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 1.72 seconds.

New terminology in this explanation is defined as follows:

 

                            Phrase

                                                     Definition

INSERT into 26 column partitions of

A single row is being inserted into 26 column partitions of table PLS.t1.

This example shows example EXPLAIN text for twice inserting the same single row into the column‑partitioned table PLS.t1.

Because column‑partitioned tables are always MULTISET, Teradata Database does not do any duplicate checking, so the insertion of duplicate rows into PLS.t1 is permitted because it has no uniqueness constraints.

     .IMPORT INDICDATA FILE=indata.dat
     .REPEAT 2 PACK 5
 
     EXPLAIN
     USING (a INTEGER, b INTEGER, c INTEGER, d INTEGER, e INTEGER, 
            f INTEGER, g INTEGER, h INTEGER, i INTEGER, j INTEGER,
            k INTEGER, l INTEGER, m INTEGER, n INTEGER, o INTEGER, 
            p INTEGER, q INTEGER, r INTEGER, s INTEGER, t INTEGER,
            u INTEGER, v INTEGER, w INTEGER, x INTEGER, y INTEGER, 
            z INTEGER)
     INSERT INTO t1 
     VALUES (a, b, c, d, e, f, g, h, i, j,k, l, m, n, o, p, q, r, s, t,
             u, v, w, x, y, z);
 
      *** Starting at Thu Sep 09 17:00:00 2010
 
      *** Help information returned. 4 rows.
      *** Total elapsed time was 1 second.
 
Explanation
---------------------------------------------------------------------------
  1) First, we do an INSERT into 26 column partitions (20 contexts) of
     PLS.t1.  The estimated time for this step is 1.72 seconds.
  -> No rows are returned to the user as the result of statement 1.
     The total estimated time is 1.72 seconds.
 
      *** Help information returned. 4 rows.
      *** Total elapsed time was 1 second.
 
      *** Finished at Thu Sep 09 17:00:00 2010
      *** Total number of statements: 2,  Accepted : 2,  Rejected : 0

New terminology in this explanation is defined as follows:

 

                      Phrase

                                                       Definition

INSERT into 26 column partitions of

A single row is being inserted into 26 column partitions of table PLS.t1.

In this case, the same duplicate row is being inserted twice.