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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
ncd1596241368722.ditamap
dita:ditavalPath
hoy1596145193032.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;