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 tables are created in database PLS. 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 Four 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 19 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 typically 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) | The rows and columns for up to 52 combined partitions of table t4 may 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 has 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 may be accessed is the delete column partition. Not all of the m1 column partitions may 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 may 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 the spool 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 may 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 this spool 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 for specifying 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 through 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....