You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Suresh Subbiah (JIRA)" <ji...@apache.org> on 2015/10/08 06:50:27 UTC
[jira] [Assigned] (TRAFODION-1472) SHOWSTATS EVERY COLUMN does not
show multi-key-column stats for salted tables
[ https://issues.apache.org/jira/browse/TRAFODION-1472?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Suresh Subbiah reassigned TRAFODION-1472:
-----------------------------------------
Assignee: David Wayne Birdsall
> 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
>
> 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)