Yeap, you're right ... View more

Alex,   Thank you again.   Subquery approach has been recommended to our team as a long term solution. However, for short-tem solution to avoid regression impact, using view with limited partitions has been selected.   If I remember correctly, in MySQL `table A` data can be limited by `ON Clause` before joining so that cadidates for join can be reduced.   Thank you for your valuable comment. Gatsby ... View more

Alex,   First of all, thank you very much for your explanation.   You're right. the second query selects partition in table A. And, I'm fully aware of the difference between the first one( using on clause ) and second on ( where clause ) like the way you explained.   The reason different variances were tried to find out ways to limit table A data before joinging two tables. ( yes, second query doesn't work this way ) In MySQL, table A could be limited by `ON clause`, but with Impala, I don't know how to do it.   Do you think using subquery is the best way?   Thank you Gatsby ... View more

Hi Henry,   I have a question for you and it is about `partition pruning` ( about pruning )   Let's say there are two tables A and B. And, each table is partitioned by yearweek. And, here is the query I'd like to run. ( Yes. I need to use left join to get result what I want ) SELECT * FROM A LEFT OUTER JOIN B ON A.account_id = B.account_id AND A.yearweek = 201710 and B.yearweek = 201710 Even this doesn't select specific partition in `table A`  SELECT * FROM A LEFT OUTER JOIN B ON A.account_id = B.account_id AND B.yearweek = 201710 WHERE A.yearweek = 201710   Like you said, `A.yearweek` = 201710 on `On clause` couldn't select partition yearweek=201710. This might be filter is applied from `Left to Right`. In order to select specific partition for `table A`. I used `dynamic partition` and updated query like this. SELECT * FROM (SELECT * FROM A WHERE yearweek = 201710) a LEFT OUTER JOIN B b ON a.account_id = b.account_id AND b.yearweek = 201710 Do you think this is best I can do? Or is there way to limit data for `table A` by using `On clause`?   And, is there any refrerence you would recommend for me to upderstand how JOIN works in Impala and Hive?   Thank you very much in advance.   Gatsby ... View more