Consider the following view definitions for the examples in this set.
CREATE VIEW v1 (i, j) AS ( SELECT x+1, b+1 FROM test); CREATE VIEW v3 (i, j) AS (SELECT CASE WHEN z1='abc' THEN 1 ELSE 0, a1+1 FROM test1);
Vantage does not copy statistics for specific column sets or indexes if the subquery specifies functions or expressions of any kind. This is because such data manipulations cause the resulting column set or index value set to be transformed into something different from that of the source relation as these examples demonstrate.
Example1
In this example, Vantage does not copy statistics because the view v1 selects column expressions from its underlying table test, so the resulting columns in the target table t1 are different from the columns in test.
CREATE TABLE t1 AS (SELECT * FROM v1) WITH NO DATA AND STATISTICS;
Example2
In this example, Vantage does not copy statistics because the view v1 selects column expressions from its underlying table test, so the resulting column data in the target table would be different from the columns in test. Also note that you cannot copy zeroed statistics.
CREATE TABLE t5 AS (SELECT * FROM v1) WITH DATA AND STATISTICS;
Example3
In this example, Vantage does not copy statistics because the view v3 is defined using a CASE expression, so the resulting column data in the target table would be different from the columns in test1.
CREATE TABLE t5 AS (SELECT * FROM v3) WITH DATA AND STATISTICS;
Example4
In this example, Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to be different from the columns in test.
CREATE TABLE t5 (c1, c2) AS (SELECT x+1, y+1 FROM test) WITH DATA AND STATISTICS;
Example5
In this example, Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to be different from the columns in test1.
CREATE TABLE t5 (c1, c2) AS (SELECT (k1/(y1+2)), b1+7 FROM test1) WITH DATA AND STATISTICS;
Example6
In this example, Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to be different from the columns in test.
CREATE TABLE t5 (a, b) AS (SELECT x+1 AS x, y+1 AS y FROM test) WITH DATA AND STATISTICS;