| --! qt:dataset:src1 |
| --! qt:dataset:src |
| set hive.mapred.mode=nonstrict; |
| set hive.auto.convert.join=false; |
| set hive.optimize.correlation=false; |
| |
| -- SORT_QUERY_RESULTS |
| |
| -- When Correlation Optimizer is turned off, 4 MR jobs are needed. |
| -- When Correlation Optimizer is turned on, 2 MR jobs are needed. |
| -- The first job will evaluate subquery xx and xx join yy. |
| EXPLAIN |
| SELECT xx.key, xx.cnt, yy.key |
| FROM |
| (SELECT x.key as key, count(1) as cnt FROM src1 x JOIN src1 y ON (x.key = y.key) group by x.key) xx |
| JOIN src yy |
| ON xx.key=yy.key ORDER BY xx.key, xx.cnt, yy.key; |
| |
| SELECT xx.key, xx.cnt, yy.key |
| FROM |
| (SELECT x.key as key, count(1) as cnt FROM src1 x JOIN src1 y ON (x.key = y.key) group by x.key) xx |
| JOIN src yy |
| ON xx.key=yy.key; |
| |
| set hive.optimize.correlation=true; |
| set hive.join.emit.interval=1; |
| EXPLAIN |
| SELECT xx.key, xx.cnt, yy.key |
| FROM |
| (SELECT x.key as key, count(1) as cnt FROM src1 x JOIN src1 y ON (x.key = y.key) group by x.key) xx |
| JOIN src yy |
| ON xx.key=yy.key; |
| |
| SELECT xx.key, xx.cnt, yy.key |
| FROM |
| (SELECT x.key as key, count(1) as cnt FROM src1 x JOIN src1 y ON (x.key = y.key) group by x.key) xx |
| JOIN src yy |
| ON xx.key=yy.key; |