Vantage copies multicolumn zeroed statistics and zeroed composite index statistics even if the relative order of the index columns specified in the select list of the subquery is not the same as their order in the source table.
Example 1: Copying Single-Column Statistics
In this example, Vantage copies the single-column statistics on columns x and a in test as zeroed single-column statistics for columns a and c in t2. The multicolumn statistics on (a,b) are not copied as zeroed multicolumn statistics on columns (b,c) in t2 because the order of columns b and c in t2 is different than the order of columns b and a in test.
CREATE TABLE t2 (a, b, c) AS (SELECT x AS colA, b, a (AS colC) FROM test) WITH NO DATA AND STATISTICS;
| Vantage copies these zeroed statistics … | From this source table column set … | To this target table column set … | Because … |
|---|---|---|---|
| single column | x | a | column a in target table t2 is the exact analog of column x in source table test. |
| y | none | there is no analog of source table column y in target table t2. | |
| single-column NUPI | a | c | column c in target table t2 is the exact analog of column a in source table test. |
| multicolumn | (a,b) | none | the order of columns b and c in the definition of target table t2 is different than the order of columns b and a in the definition of source table test. |
| (x,y,z) | there is no multicolumn index set defined for source table t2 on an analogous column set to (x,y,z) and (x,y) in source table test. | ||
| multicolumn NUSI | (x,y) | none | there is no multicolumn index set defined for source table t2 on an analogous column set to (x,y,z) and (x,y) in source table test. |
Example 2: Copying Single-Column Statistics
In this example, Vantage copies the single-column statistics from test1 as zeroed single-column statistics on columns a , b , and c in t2. The index statistics on (x1,y1) are not copied as zeroed index statistics on columns (a,b) in t2 because the order of columns a and b in t2 is different than the order of columns x 1 and y1 in test1.
CREATE TABLE t2 (a, b, c) AS (SELECT k1 AS colA, y1 AS colB, z1 AS colC) FROM test1) WITH NO DATA AND STATISTICS INDEX(a, b);
| Vantage copies these zeroed statistics … | From this source table column set … | To this target table column set … | Because … |
|---|---|---|---|
| single column | k1 | a | the new columns in target table t2 are exact analogs of the existing columns in source table test1. |
| y1 | b | ||
| z1 | c | ||
|
none | there are no analogs of these source table columns defined in target table t2. | |
| single-column NUSI | z1 | none | there is no single-column NUSI defined on the analog to source table column z1, column c, in the source table; instead, there is a multicolumn NUSI defined on the column set (a,b). The NUSI on (a,b) in target table t2 is defined without statistics being copied because no statistics were collected on their analog column set, (k1,y1), in source table test1. |
| multicolumn NUPI | (x1,y1) | none | the order of the index columns specified in the subquery select list is different than the column order of the test1 index. |