[jira] [Created] (TRAFODION-355) LP Bug: 1327395 - Update Stats "on every key" fails to create histograms.

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-355:
------------------------------------

             Summary: LP Bug: 1327395 - Update Stats "on every key" fails to create histograms.
                 Key: TRAFODION-355
                 URL: https://issues.apache.org/jira/browse/TRAFODION-355
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Barry Fritchman
            Assignee: Barry Fritchman
            Priority: Critical


Performing an Update Statistics operation that specifies ON EVERY KEY fails to generate any histograms if the clustering index consists of multiple columns. For example:

CREATE TABLE G_TPCH2X.LINEITEM
  (
    L_ORDERKEY                       INT NO DEFAULT NOT NULL
  , L_PARTKEY                        INT NO DEFAULT NOT NULL
  , L_SUPPKEY                        INT NO DEFAULT NOT NULL
  , L_LINENUMBER                     INT NO DEFAULT NOT NULL
  , L_QUANTITY                       NUMERIC(12, 2) NO DEFAULT NOT NULL
  , L_EXTENDEDPRICE                  NUMERIC(12, 2) NO DEFAULT NOT NULL
  , L_DISCOUNT                       NUMERIC(12, 2) NO DEFAULT NOT NULL
  , L_TAX                            NUMERIC(12, 2) NO DEFAULT NOT NULL
  , L_RETURNFLAG                     CHAR(1) CHARACTER SET ISO88591  NO DEFAULT
      NOT NULL
  , L_LINESTATUS                     CHAR(1) CHARACTER SET ISO88591  NO DEFAULT
      NOT NULL
  , L_SHIPDATE                       DATE NO DEFAULT NOT NULL
  , L_COMMITDATE                     DATE NO DEFAULT NOT NULL
  , L_RECEIPTDATE                    DATE NO DEFAULT NOT NULL
  , L_SHIPINSTRUCT                   CHAR(25) CHARACTER SET ISO88591  NO
      DEFAULT NOT NULL
  , L_SHIPMODE                       CHAR(10) CHARACTER SET ISO88591  NO
      DEFAULT NOT NULL
  , L_COMMENT                        VARCHAR(44) CHARACTER SET ISO88591  NO
      DEFAULT NOT NULL
  , PRIMARY KEY (L_SHIPDATE ASC, L_ORDERKEY ASC, L_LINENUMBER ASC)
  )
salt using 4 partitions
  ;

Executing this statement:
UPDATE STATISTICS FOR TABLE G_TPCH2X.LINEITEM ON EVERY KEY;

Fails to create any histograms and elicits the following warning:
*** WARNING[9213] If you intend to update histogram statistics for columns, you must specify a column list in the statement.

The workaround is to explicitly specify the multicolumn group corresponding to the index in the Update Stats statement. For the example above, this would be:
UPDATE STATISTICS FOR G_TPCH2X.TABLE LINEITEM ON ("_SALT_", L_SHIPDATE, L_ORDERKEY, L_LINENUMBER);



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)