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

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/TRAFODION-1467?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15144883#comment-15144883 ] 

David Wayne Birdsall commented on TRAFODION-1467:
-------------------------------------------------

Debugged this today. Found the following code in sql/ustat/hs_parser.cpp:

        // For salted table, generate only the longest MC for the key (subject
        // to max cols determined above) unless a cqd is set to gen all MCs of
        // allowable sizes.
        if (CmpCommon::getDefault(USTAT_ADD_SALTED_KEY_PREFIXES_FOR_MC) == DF_OFF &&
            hs_globals->objDef->getColNum("_SALT_", FALSE) >= 0)
          minMCGroupSz = numKeys;

And indeed, the default setting for the CQD USTAT_ADD_SALTED_KEY_PREFIXES_FOR_MC is 'OFF'. So the behavior described in this case is intentional. I don't know why yet. I've reached out by e-mail to the Trafodion development community to see if I can learn some history.

But in any case, this shows that there is a workaround: Just set CQD USTAT_ADD_SALTED_KEY_PREFIXES_FOR_MC 'ON' prior to issuing UPDATE STATISTICS. Then you'll get a more complete set of key prefixes. (The USTATS code does limit prefixes to 5 columns however.)

> 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
>
> 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)