[ https://issues.apache.org/jira/browse/TRAFODION-1467?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
David Wayne Birdsall updated TRAFODION-1467: -------------------------------------------- Fix Version/s: 2.0-incubating > Update statistics does not generate multi-column key stats on a salted table > ---------------------------------------------------------------------------- > > Key: TRAFODION-1467 > URL: https://issues.apache.org/jira/browse/TRAFODION-1467 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmu > Affects Versions: 1.1 (pre-incubation) > Reporter: Rohit Jain > Assignee: David Wayne Birdsall > Priority: Minor > Labels: performance > Fix For: 2.0-incubating > > > The EVERY KEY or EVERY COLUMN option in update statistics generates > multi-column statistics for all the key columns. It does not do this if the > table is salted, potentially leading to poor plans. > Example of when it works: > create table t1 (a int NOT NULL NOT DROPPABLE, b int NOT NULL NOT DROPPABLE, > c int NOT NULL NOT DROPPABLE, primary key (a, b, c)); > update statistics for table t1 on every key; > showstats for table t1 on existing columns; > Histogram data for Table TRAFODION.BCI.T1 > Table ID: 7427073929238755441 > Hist ID # Ints Rowcount UEC Colname(s) > ========== ====== =========== =========== =========================== > 868236218 1 0 0 C > 868236223 1 0 0 B > 868236228 1 0 0 A > 868236233 1 0 0 A, B > 868236238 1 0 0 A, B, C > Example of when it doesn't (salted table): > create table t1 (a int NOT NULL NOT DROPPABLE, b int NOT NULL NOT > DROPPABLE, c int NOT NULL NOT DROPPABLE, primary key (a, b, c)) salt using 4 > partitions on (a, b); > update statistics for table t1 on every key; > showstats for table t1 on existing columns; > Histogram data for Table TRAFODION.BCI.T1 > Table ID: 7427073929238776270 > Hist ID # Ints Rowcount UEC Colname(s) > ========== ====== =========== =========== =========================== > 998895314 1 0 0 C > 998895319 1 0 0 B > 998895324 1 0 0 A > 998895329 1 0 0 "_SALT_" > 998895334 1 0 0 "_SALT_", A, B, C -- This message was sent by Atlassian JIRA (v6.3.4#6332)