15.10 - Example: Statistics Are Not Copied If Functions Or Expressions Are Specified in the Subquery - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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;