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.
Apologies for a very late repsonse on this... couldnt reply due to some personal concerns.
The ask here is,
Table A has a key column and the date value as 31/01/2019.
Table B has the same key column and date value as 29/01/2019. (There is no 31/01/2019 in Table B)
Both are Monthly partitioned table.
So the join should be something like Table A.Key_Column = Table B.Key_Column
and Table A.date_Value/100 = Table B.Date_Value/100
But this join will not be qualified for the dynamic partition elimination. Tried with Last_day and TD_Month_Of_Calendar as well.
Thanks for your inputs.
I tried the option and this also going for all rows scan... didnt qualify for the dynamic partition elimination...
I believe any date function used on the partition column will cause for full table scan. Please correct me if my understanding is wrong.