[TRAFODION-1472] SHOWSTATS EVERY COLUMN does not show multi-key-column stats for salted tables

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.1 (pre-incubation)
• Fix Version/s: 2.0-incubating
• Component/s: sql-cmu
• Assignee: Dave Birdsall
• Reporter: Rohit Jain
• Created: 29/Aug/15 04:24
• Updated: 21/Apr/16 22:32
• Resolved: 18/Feb/16 16:57

Description

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.