Example: Statistics Are Not Copied If Functions or Expressions Are Specified in the Subquery - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

Consider the following view definitions:

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. Such data manipulations would transform the resulting column set or index value set to something different from that of the source relation, as these examples show.

Example1

Vantage does not copy statistics because view v1 selects column expressions from its underlying table test, so the resulting columns in the target table t1 would differ from the columns in test.

CREATE TABLE t1 AS (SELECT *
  FROM v1)
  WITH NO DATA AND STATISTICS;

Example2

Vantage does not copy statistics because view v1 selects column expressions from its underlying table test, so the resulting column data in the target table would differ from the columns in test. You cannot copy zeroed statistics.

CREATE TABLE t5 AS (SELECT *
  FROM v1) 
  WITH DATA AND STATISTICS;

Example3

Vantage does not copy statistics because view v3 uses a CASE expression, so the resulting column data in the target table would differ from the columns in test1.

CREATE TABLE t5 AS (SELECT *
  FROM v3) 
  WITH DATA AND STATISTICS;

Example4

Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to differ from the columns in test.

CREATE TABLE t5 (c1, c2) AS (SELECT x+1, y+1
  FROM test) 
  WITH DATA AND STATISTICS;

Example5

Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to differ from the columns in test1.

CREATE TABLE t5 (c1, c2) AS (SELECT (k1/(y1+2)), b1+7
  FROM test1) 
  WITH DATA AND STATISTICS;

Example6

Vantage does not copy statistics because the subquery specifies simple arithmetic expressions that would cause the data in the target table to differ 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;