I would like to post a query here.
There are two large monthly snapshot tables which are partitioned by business date on Monthly interval.
PARTITION BY(RANGE_N(BUS_DATE BETWEEN DATE '2018-06-01' AND ADD_MONTHS(DATE,2) EACH INTERVAL '1' MONTH NO RANGE , UNKNOWN)
If any query joining these two tables by PPI then it is qualifed for dynamic partition elimination.
How do I join these tables based on only month & Year and qualify for dynamic partition elimination.
Extract(year from TableA.Bus_Date) = Extract(year from TableB.Bus_Date)
and Extract(month from TableA.Bus_Date) = Extract(month from TableB.Bus_Date)
Appreciate your response on this.
You will have to explain what you mean by "joining based on month and year." If I take that literally, I can only imagine a monstrous cartesian join of these tables - i.e., any single row for June in TableA would be joined to every June row in TableB. If TableA had 1000 rows and TableB had a million rows, your answer set would be about 500,000,000 rows. Surely that is not what you want.