Example: Statistics Are Not Copied for Conditions in The Subquery - Teradata Vantage - Analytics Database

Vantage 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, Vantage 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, Vantage 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, Vantage 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, Vantage 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;