| --! qt:dataset:src1 |
| set hive.mapred.mode=nonstrict; |
| -- query from auto_sortmerge_join_9.q |
| |
| CREATE TABLE tbl1_n9(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; |
| CREATE TABLE tbl2_n8(key int, value string) CLUSTERED BY (key) SORTED BY (key) INTO 2 BUCKETS; |
| |
| set hive.auto.convert.join=true; |
| set hive.optimize.bucketmapjoin = true; |
| set hive.optimize.bucketmapjoin.sortedmerge = true; |
| set hive.auto.convert.sortmerge.join=true; |
| set hive.auto.convert.sortmerge.join.to.mapjoin=false; |
| |
| set hive.explain.dependency.append.tasktype=true; |
| |
| -- default behavior |
| |
| explain |
| select src1.key, src1.cnt1, src2.cnt1 from |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq1 group by key |
| ) src1 |
| join |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq2 group by key |
| ) src2 |
| on src1.key = src2.key |
| order by src1.key, src1.cnt1, src2.cnt1; |
| |
| set hive.stageid.rearrange=IDONLY; |
| |
| -- changes id only |
| |
| explain |
| select src1.key, src1.cnt1, src2.cnt1 from |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq1 group by key |
| ) src1 |
| join |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq2 group by key |
| ) src2 |
| on src1.key = src2.key |
| order by src1.key, src1.cnt1, src2.cnt1; |
| |
| set hive.stageid.rearrange=TRAVERSE; |
| |
| -- assign ids in traverse order |
| |
| explain |
| select src1.key, src1.cnt1, src2.cnt1 from |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq1 group by key |
| ) src1 |
| join |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq2 group by key |
| ) src2 |
| on src1.key = src2.key |
| order by src1.key, src1.cnt1, src2.cnt1; |
| |
| set hive.stageid.rearrange=EXECUTION; |
| |
| -- assign ids in execution order |
| |
| explain |
| select src1.key, src1.cnt1, src2.cnt1 from |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq1 group by key |
| ) src1 |
| join |
| ( |
| select key, count(*) as cnt1 from |
| ( |
| select a.key as key, a.value as val1, b.value as val2 from tbl1_n9 a join tbl2_n8 b on a.key = b.key |
| ) subq2 group by key |
| ) src2 |
| on src1.key = src2.key |
| order by src1.key, src1.cnt1, src2.cnt1; |