Rohit Jain created TRAFODION-1472:
-------------------------------------
Summary: SHOWSTATS EVERY COLUMN does not show multi-key-column
stats for salted tables
Key: TRAFODION-1472
URL: https://issues.apache.org/jira/browse/TRAFODION-1472
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmu
Affects Versions: 1.1 (pre-incubation)
Reporter: Rohit Jain
Priority: Minor
If Update Statistics is done on an unsalted table using the EVERY KEY option
not only are all leading multi-key-column statistics gathered but SHOWSTATS
display these statistics either with the EVERY COLUMN OPTION or the EXISTING
COLUMNS option. It does not do so for tables that are salted.
Works for table that is not salted
-----------------------------------------
create table t2 (cust int not null not droppable, account int not null not
droppable, txn_timestamp timestamp not null not droppable, amt numeric (18,4),
primary key (cust, account, txn_timestamp));
update statistics for table t2 on every key;
showstats for table t2 on every column;
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1409472594 1 0 0 CUST
1409472589 1 0 0 ACCOUNT
1409472584 1 0 0 TXN_TIMESTAMP
1409472604 1 0 0 CUST, ACCOUNT, TXN_TIMESTAMP
1409472599 1 0 0 CUST, ACCOUNT
showstats for table t2 on existing columns;
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
1409472584 1 0 0 TXN_TIMESTAMP
1409472589 1 0 0 ACCOUNT
1409472594 1 0 0 CUST
1409472599 1 0 0 CUST, ACCOUNT
1409472604 1 0 0 CUST, ACCOUNT, TXN_TIMESTAMP
Does not work for table that is salted
-----------------------------------------------
create table t1 (cust int not null not droppable, account int not null not
droppable, txn_timestamp timestamp not null not droppable, amt numeric (18,4),
primary key (cust, account, txn_timestamp)) salt using 8 partitions on (cust,
account) division by (date_part('YEARMONTH', txn_timestamp));
update statistics for table t1 on every key;
This does not generate leading multi-key-column statistics as reported in
another JIRA. So follow this by:
update statistics for table t1 on ("_SALT_", "_DIVISION_1_"), ("_SALT_",
"_DIVISION_1_", CUST), ("_SALT_", "_DIVISION_1_", CUST, ACCOUNT);
showstats for table t1 on every column;
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
270748103 1 0 0 CUST
270748096 1 0 0 ACCOUNT
270748092 1 0 0 TXN_TIMESTAMP
270748113 1 0 0 "_SALT_"
270748106 1 0 0 "_DIVISION_1_"
270748117 1 0 0 "_SALT_", "_DIVISION_1_", CUST,
ACCOUNT, TXN_TIMESTAMP
showstats for table t1 on existing columns;
Hist ID # Ints Rowcount UEC Colname(s)
========== ====== =========== =========== ===========================
270748092 1 0 0 TXN_TIMESTAMP
270748096 1 0 0 ACCOUNT
270748103 1 0 0 CUST
270748106 1 0 0 "_DIVISION_1_"
270748113 1 0 0 "_SALT_"
270748117 1 0 0 "_SALT_", "_DIVISION_1_", CUST,
ACCOUNT, TXN_TIMESTAMP
270748122 1 0 0 "_SALT_", "_DIVISION_1_"
270748127 1 0 0 "_SALT_", "_DIVISION_1_", CUST
270748132 1 0 0 "_SALT_", "_DIVISION_1_", CUST,
ACCOUNT
The EXISTING COLUMNS option displays all leading multi-key-column statistics,
ON EVERY COLUMN doesn't.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
