17.00 - Example: Statistics Are Not Copied for Conditions in The Subquery - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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;