16.20 - Example: Statistics Are Not Copied For Conditions 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

Teradata Database does not copy statistics if any single table conditions or join conditions are specified in the WHERE clause of the subquery as these examples demonstrate. A warning message is returned to the requestor that says statistics cannot be copied.

Example1

In this example, Teradata Database does not copy statistics because only partial data is copied based on the WHERE condition that limits copied rows to those having a value for column x greater than 10.

    CREATE TABLE t2 AS (SELECT *
                        FROM test 
                        WHERE x > 10) 
    WITH DATA AND STATISTICS;

Example2

In this example, Teradata Database does not copy statistics because only partial data is copied based on the WHERE clause in the subquery that limits copied rows to those having a value for column x1 greater than 10 AND a value for column y1 equal to 20.

    CREATE TABLE t2 AS (SELECT *
                        FROM test1 
                        WHERE x1 > 10 
                        AND   y1 = 20) 
    WITH DATA AND STATISTICS;

Example3

In this example, Teradata Database does not copy statistics because tables are left outer joined in the subquery.

    CREATE TABLE t2 AS (SELECT *
                        FROM test LEFT OUTER JOIN                                   test1 ON test.x=test1.x1) 
    WITH DATA AND STATISTICS;

Example4

In this example, Teradata Database does not copy statistics because tables are right outer joined in the subquery.

     CREATE TABLE t2 (a, b) AS (SELECT x1, y1
                                FROM test RIGHT JOIN 
                                test1 ON test. a=test1.a1) 
     WITH DATA AND STATISTICS;