16.20 - Example: Statistics Are Not Copied If Functions Or Expressions Are Specified in the Subquery - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

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

Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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, Teradata Database 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;