[
https://issues.apache.org/jira/browse/TRAFODION-2166?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15426857#comment-15426857
]
David Wayne Birdsall commented on TRAFODION-2166:
-------------------------------------------------
Just for fun, I tried a similar test on normal Trafodion tables. UPDATE STATS
ON NECESSARY COLUMNS doesn't work there either. So I think this is a case of
where a feature from the predecessor product did not get carried over
completely to Trafodion when the latter code was ported to HBase. Here's the
script I tried:
>>obey normal.sql;
>>-- make sure ON NECESSARY COLUMNS works in the normal case
>>create table custo_demo
+>(
+> CD_DEMO_SK INT not null not droppable
+> , CD_GENDER char(1) CHARACTER SET UTF8
+> COLLATE DEFAULT
+> , CD_MARITAL_STATUS char(1) CHARACTER SET UTF8
+> COLLATE DEFAULT
+> , CD_EDUCATION_STATUS char(18) CHARACTER SET UTF8
+> COLLATE DEFAULT
+> , CD_PURCHASE_ESTIMATE INT
+> , CD_CREDIT_RATING char(12) CHARACTER SET UTF8
+> COLLATE DEFAULT
+> , CD_DEP_COUNT INT
+> , CD_DEP_EMPLOYED_COUNT INT
+> , CD_DEP_COLLEGE_COUNT INT
+> , primary key (cd_demo_sk)
+>)
+>salt using 4 partitions;
--- SQL operation complete.
>>
>>-- to turn on ability to use ON NECESSARY COLUMNS
>>CQD USTAT_AUTOMATION_INTERVAL '100';
--- SQL operation complete.
>>
>>-- so UPDATE STATS doesn't take so damned long
>>cqd hive_max_string_length '100';
--- SQL operation complete.
>>
>>upsert using load into custo_demo select * from
>>hive.hive.customer_demographics;
--- 1920800 row(s) inserted.
>>
>>-- should get 6011 error on join column, but no reason = ' ' row gets
>>generated
>>prepare s1 from
+> select count(*)
+> from custo_demo x join custo_demo y
+> on x.cd_purchase_estimate = y.cd_purchase_estimate;
*** WARNING[6011] Statistics for column (CD_DEMO_SK) from table
TRAFODION.SEABASE.CUSTO_DEMO were not available. As a result, the access path
chosen might not be the best possible. These statistics will be automatically
generated at the next opportunity; however, you can generate them now by using
UPDATE STATISTICS FOR TABLE TRAFODION.SEABASE.CUSTO_DEMO ON NECESSARY COLUMNS.
*** WARNING[6011] Statistics for column (CD_PURCHASE_ESTIMATE) from table
TRAFODION.SEABASE.CUSTO_DEMO were not available. As a result, the access path
chosen might not be the best possible. These statistics will be automatically
generated at the next opportunity; however, you can generate them now by using
UPDATE STATISTICS FOR TABLE TRAFODION.SEABASE.CUSTO_DEMO ON NECESSARY COLUMNS.
--- SQL command prepared.
>>
>>-- should work
>>update statistics for table custo_demo on necessary columns;
*** WARNING[9218] The statement will have no effect because no histograms need
to be updated.
--- SQL operation completed with warnings.
>>get tables;
Tables in Schema TRAFODION.SEABASE
==================================
CUSTO_DEMO
SB_HISTOGRAMS
SB_HISTOGRAM_INTERVALS
SB_PERSISTENT_SAMPLES
--- SQL operation complete.
>>select count(*) From sb_histograms;
(EXPR)
--------------------
0
--- 1 row(s) selected.
>>
> UPDATE STATS ON NECESSARY COLUMNS does not work on Hive tables
> --------------------------------------------------------------
>
> Key: TRAFODION-2166
> URL: https://issues.apache.org/jira/browse/TRAFODION-2166
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Environment: All
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
