CREATE set table xxxxx.x2 AS MULTISET TABLE xxxx.x1 WITH DATA AND STAT - NO stats copied.

[achikan01]

CREATE set table xxxxx.x2  AS MULTISET TABLE xxxxx.x1 WITH DATA AND STAT  - Data copied , but no stats copied.

create MULTISET  table  XXXXX.test_1

(

EMPNO integer,

EMPNAME VARCHAR(50),

EMPSALARY INTEGER

)

PRIMARY INDEX(EMPNO);

INSERT INTO XXXXX.TEST_1

VALUES(1,'ABCD',1000);

INSERT INTO XXXXX.TEST_1

VALUES(2,'EFGH',2000);

INSERT INTO XXXXX.TEST_1

VALUES(3,'IJKLM',1000);

COLLECT STATISTICS 

            COLUMN ( EMPNO) , 

            COLUMN ( EMPNO,EMPSALARY ) 

                ON XXXXX.TEST_1 ;

  HELP STAT XXXXX.TEST_1;

15/01/14 15:44:31                   3 * * "*" ?

15/01/14 15:44:31                   3 EMPNO,EMPSALARY EMPNO,EMPSALARY EMPNO,EMPSALARY ?

15/01/14 15:44:31                   3 EMPNO EMPNO EMPNO ?

  create SET  table XXXXX.TEST_2 AS XXXXX.TEST_1 with data and stats;

  HELP STAT XXXXX.TEST_2

There are no statistics defined for the table. 

Stat information missing on copy. Why?

[Rohan_Sawant]

Hi Achikan01,

That because you defined the first table as "MULTISET" and the second as "SET". Whenever you convert "MULTISET" to "SET" duplicate records will get dropped and obviously original stats would become useless if there are duplicates  and hence stats are not collected because of the same reason. Creating another table as "MULTISET" you will see stats are not getting dropped.

CREATE MULTISET TABLE XXXXX.TEST_3 AS XXXXX.TEST_1 WITH DATA AND STATS;
 
HELP STAT XXXXX.TEST_3;

Date Time Unique Values Column Names Column Dictionary Names Column SQL Names Column Names UEscape

1 15/01/28 17:23:29                   3 * * "*" ?

2 15/01/28 17:23:29                   3 EMPNO,EMPSALARY EMPNO,EMPSALARY EMPNO,EMPSALARY ?

3 15/01/28 17:23:29                   3 EMPNO EMPNO EMPNO ?

Please let me know in case you still have queries.

Thanks,

Rohan Sawant

[achikan01]

 There are no dupicates on multiset tables.My questions is why stats are  not copied from mutiset table to set table. It is not useless stat. I know stats will be copied  from mutiset table to multiset table.

[Rohan_Sawant]

Hi,

I didnt say that your table has duplicates. I said that MULTISET to SET is removal of duplicates. Teradata knows that and hence it sets up a standard to not pass the stats since there is a chance of duplicates to come in future. While passing stats the data is not checked. It only sees MULTISET to SET and does it. Its a standard set which we cant change and seems logical to me.

Thanks,

Rohan Sawant

[dnoeth]

Btw, this behaviour is fully documented in the manuals:

If all columns in a MULTISET source table are non-unique, and if the target table is a SET table, then the system does not copy statistics to the target table. This is because of the possible violation of the rule of equal cardinalities in the source and target tables: if there are duplicate rows in the source table, the system eliminates them before copying to the target table, resulting in unequal cardinalities between the 2 tables.