17.10 - EXPLAIN Request Modifier and Column-Partition Access - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

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

Table Definitions for the Examples

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);

Selecting a Few Columns from a Column-Partitioned Table

This example shows part of the 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;
...
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.
...

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. There are sufficient contexts to process all five partitions at once.

Selecting Many Columns From a Column-Partitioned Table

This example shows part of the 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;
...
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.
...

Selecting More Columns From a Column-Partitioned Table Than There Are Contexts Available

This example shows part of the 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;
...
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.
...

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 on this system, so Vantage 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.

Selecting All Columns From a Column-Partitioned Table

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

EXPLAIN 
SELECT *
FROM t1;
...
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.
...

Selecting All Columns From a Multilevel Column-Partitioned and Row-Partitioned Table

This example shows part of the 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;
...
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.
...

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.

Selecting Multiple Columns From a Multilevel Column- and Row-Partitioned Table

This example shows part of the 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;
...
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.
...

Selecting Columns From a Multilevel Column- and Row-Partitioned Table

This example shows part of the 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;
...
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 builtlocally on the AMPs. The size of Spool 1 is estimated 
   with noconfidence to be 1 row (307 bytes). The estimated
   time for this step is 0.03 seconds.
...

Selecting 2 Columns From a Column-Partitioned Table With a Predicate Specified for 26 Columns in the WHERE Clause

This example shows part of the 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;
...
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.
...

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.

Selecting 21 Columns From a Column-Partitioned Table With a Predicate Specified for 26 Columns in the WHERE Clause

This example shows part of the 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;
...
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.
...

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.

INSERT … SELECT from Nonpartitioned Table with Primary Index into Column-Partitioned Table Using Default Locking

This example shows part of the 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;
...
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.
...

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.

INSERT ... SELECT from Nonpartitioned Table With Primary Index into Column-Partitioned Table Using LOCKING Modifier

This example shows part of the 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;
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.

INSERT ... SELECT from Nonpartitioned Source Table with Primary IndexiInto Column-Partitioned Target Table

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

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.

Merge 20 columns into t1.

Merge the remaining 6 columns into t1.

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

INSERT ... SELECT from Nonpartitioned Source Table with Primary Index and Locking Request into Column-partitioned Target Table

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 the previous example, 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;
...
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.
...

INSERT ... SELECT from Column-Partitioned Source Table into Column-Partitioned Target Table

This example shows part of the 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;
...
4) We do an all-AMPs RETRIEVE step from 27 column partitions
   (20 contexts) of PLS.t3 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.
...

This example does not introduce any new EXPLAIN text terminology.

Inserted Column Data from VALUES Clause

This example shows part of the 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,,,,,,,,,,,,,,,,,,,,,,,,);
1) First, we do an INSERT into 26 column partitions  of PLS.t1. 
   The estimated time for this step 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.

Inserted Column Data from USING Request Modifier

This example shows part of the 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);
1) First, we do an INSERT into 26 column partitions of 
   PLS.t1. The estimated time for this step is 1.72 seconds....