We have an external hive table with a partition column that is of type varchar2. This table is left outer joined to another table with the partition field in the join condition.
In the query below the column b2 from the table b is the partition column of type varchar2, and a2 from the table a has only 1 value and is also of type varchar2.
SELECT a.a1, b.b1
FROM a LEFT OUTER JOIN b ON (a.a2=b.b2)
When I issue an EXPLAIN PLAN on the above query I see all of the partitions under the table b. How can I only get the partition from the table b only when it equals the value in a2 when the mapper reads the table b?
Select query get executed /planed in below sequence .
FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP
Query have join and where and as per rule first join condition will be evaluated then where clause hence u are seeing all partition of Table B in PLAN