| set hive.tez.container.size = 8192; |
| set hive.stats.fetch.column.stats=true; |
| set hive.cbo.enable=true; |
| set hive.tez.dynamic.partition.pruning=true; |
| set hive.exec.dynamic.partition.mode=nonstrict; |
| set hive.optimize.metadataonly=true; |
| set hive.optimize.reducededuplication=true; |
| set hive.optimize.null.scan=true; |
| set hive.mapjoin.optimized.hashtable=true; |
| set hive.optimize.constant.propagation=true; |
| set hive.optimize.index.filter=true; |
| set hive.optimize.bucketmapjoin=true; |
| set hive.limit.optimize.enable=true; |
| set hive.optimize.bucketmapjoin.sortedmerge=false; |
| set hive.optimize.reducededuplication.min.reducer=1; |
| set hive.vectorized.execution.reduce.enabled=true; |
| set hive.auto.convert.join=true; |
| set hive.vectorized.execution.mapjoin.native.enabled=true; |
| set hive.optimize.dynamic.partition.hashjoin=true; |
| set hive.tez.bucket.pruning=true; |
| |
| set hive.vectorized.execution.enabled=true; |
| |
| -- set hive.optimize.dynamic.partition.hashjoin=false; |
| |
| CREATE EXTERNAL TABLE item(ID int, S_ID int, NAME string); |
| |
| CREATE EXTERNAL TABLE odetail(ID int, O_DATE timestamp); |
| |
| CREATE EXTERNAL TABLE ytday(D_DATE timestamp, YTD_DATE timestamp ); |
| |
| CREATE EXTERNAL TABLE lday(D_DATE timestamp, LY_DATE timestamp); |
| |
| |
| INSERT INTO item values(101, 22, "Item 101"); |
| INSERT INTO item values(102, 22, "Item 102"); |
| |
| INSERT INTO odetail values(101, '2001-06-30 00:00:00'); |
| INSERT INTO odetail values(102, '2002-06-30 00:00:00'); |
| |
| INSERT INTO ytday values('2008-04-30 00:00:00', '2001-06-30 00:00:00'); |
| INSERT INTO ytday values('2008-04-30 00:00:00', '2022-06-30 00:00:00'); |
| |
| INSERT INTO lday values('2021-06-30 00:00:00', '2001-06-30 00:00:00'); |
| INSERT INTO lday values('2022-06-30 00:00:00', '2002-06-30 00:00:00'); |
| |
| analyze table item compute statistics; |
| analyze table item compute statistics for columns; |
| |
| analyze table odetail compute statistics; |
| analyze table odetail compute statistics for columns; |
| |
| analyze table ytday compute statistics; |
| analyze table ytday compute statistics for columns; |
| |
| analyze table lday compute statistics; |
| analyze table lday compute statistics for columns; |
| |
| EXPLAIN VECTORIZATION DETAIL |
| select * from |
| (select item1.S_ID S_ID, |
| ytday1.D_DATE D_DATE |
| from odetail od1 |
| join ytday ytday1 |
| on (od1.O_DATE = ytday1.YTD_DATE) |
| join item item1 |
| on (od1.ID = item1.ID) |
| where (item1.S_ID in (22) |
| and ytday1.D_DATE = '2008-04-30 00:00:00') |
| group by item1.S_ID, |
| ytday1.D_DATE |
| ) pa11 |
| full outer join |
| (select item2.S_ID S_ID, |
| ytday2.D_DATE D_DATE |
| from odetail od2 |
| join lday lday2 -- map8 |
| on (od2.O_DATE = lday2.LY_DATE) |
| join ytday ytday2 |
| on (lday2.D_DATE = ytday2.YTD_DATE) |
| join item item2 |
| on (od2.ID = item2.ID) |
| where (item2.S_ID in (22) |
| and ytday2.D_DATE = '2008-04-30 00:00:00') |
| group by item2.S_ID, |
| ytday2.D_DATE |
| ) pa12 |
| on (pa11.D_DATE = pa12.D_DATE and |
| pa11.S_ID = pa12.S_ID); |
| |
| select * from |
| (select item1.S_ID S_ID, |
| ytday1.D_DATE D_DATE |
| from odetail od1 |
| join ytday ytday1 |
| on (od1.O_DATE = ytday1.YTD_DATE) |
| join item item1 |
| on (od1.ID = item1.ID) |
| where (item1.S_ID in (22) |
| and ytday1.D_DATE = '2008-04-30 00:00:00') |
| group by item1.S_ID, |
| ytday1.D_DATE |
| ) pa11 |
| full outer join |
| (select item2.S_ID S_ID, |
| ytday2.D_DATE D_DATE |
| from odetail od2 |
| join lday lday2 -- map8 |
| on (od2.O_DATE = lday2.LY_DATE) |
| join ytday ytday2 |
| on (lday2.D_DATE = ytday2.YTD_DATE) |
| join item item2 |
| on (od2.ID = item2.ID) |
| where (item2.S_ID in (22) |
| and ytday2.D_DATE = '2008-04-30 00:00:00') |
| group by item2.S_ID, |
| ytday2.D_DATE |
| ) pa12 |
| on (pa11.D_DATE = pa12.D_DATE and |
| pa11.S_ID = pa12.S_ID); |