[TRAFODION-1467] Update statistics does not generate multi-column key stats on a salted table

• 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
• Labels: performance
• Assignee: Dave Birdsall
• Reporter: Rohit Jain
• Created: 26/Aug/15 17:50
• Updated: 21/Apr/16 22:32
• Resolved: 18/Feb/16 16:56

Description

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