| -- SORT_QUERY_RESULTS |
| |
| set hive.support.concurrency=true; |
| set hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager; |
| set hive.strict.checks.cartesian.product=false; |
| set hive.stats.fetch.column.stats=true; |
| set hive.materializedview.rewriting=true; |
| |
| create table if not exists source_table_001 ( |
| MY_DATE date, |
| MY_ID bigint, |
| MY_ID2 bigint, |
| ENVIRONMENT string, |
| DOWN_VOLUME bigint, |
| UP_VOLUME bigint |
| ) |
| stored AS ORC |
| TBLPROPERTIES("transactional"="true"); |
| insert into table source_table_001 |
| values ('2010-10-10', 1, 1, 'env', 1, 1); |
| |
| CREATE MATERIALIZED VIEW source_table_001_mv AS |
| SELECT |
| SUM(A.DOWN_VOLUME) AS DOWN_VOLUME_SUM, |
| SUM(A.UP_VOLUME) AS UP_VOLUME_SUM, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| from source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE |
| LIMIT 100; |
| |
| explain |
| select |
| 1, |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) + 0 AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| -- DOES NOT WORK - PROBLEM IN FIELD TRIMMER WITH OBY |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE |
| ORDER BY A.MY_ID2 |
| LIMIT 100; |
| |
| -- WORKS WITH COLUMN STATS, CBO FAILS WITHOUT |
| explain |
| select |
| distinct A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A; |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| where A.MY_DATE=TO_DATE('2010-01-10') |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) + SUM(A.UP_VOLUME) AS TOTAL_VOLUME_BYTES, |
| A.MY_DATE,A.MY_ID2,A.ENVIRONMENT |
| FROM source_table_001 AS A |
| where A.MY_DATE=TO_DATE('2010-01-10') |
| group by A.MY_ID,A.MY_ID2,A.ENVIRONMENT,A.MY_DATE; |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES |
| FROM source_table_001 AS A |
| where A.MY_DATE=TO_DATE('2010-01-10'); |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| TO_DATE('2010-01-10') |
| FROM source_table_001 AS A |
| where A.MY_DATE=TO_DATE('2010-01-10'); |
| |
| explain |
| select |
| SUM(A.DOWN_VOLUME) AS DOWNLOAD_VOLUME_BYTES, |
| A.MY_DATE |
| FROM source_table_001 AS A |
| where A.MY_DATE=TO_DATE('2010-01-10') |
| group by A.MY_DATE; |
| |
| drop materialized view source_table_001_mv; |