[ https://issues.apache.org/jira/browse/TRAFODION-1472?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall resolved TRAFODION-1472. --------------------------------------------- Resolution: Fixed Fix Version/s: 2.0-incubating Fixed by changing default for CQD USTAT_ADD_SALTED_KEY_PREFIXES_FOR_MC to 'ON'. > 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 > Assignee: David Wayne Birdsall > Priority: Minor > Fix For: 2.0-incubating > > > 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)