Partition Elimination

Database
Enthusiast

Partition Elimination

Hi All,

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.

For example,

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.

Thanks.

5 REPLIES 5
Teradata Employee

Re: Partition Elimination

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.

Enthusiast

Re: Partition Elimination

Hi,

 

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.

Junior Contributor

Re: Partition Elimination

Try

Table A.Key_Column between trunc(Table B.Key_Column, 'mm) and last_day(
Table B.Date_Value)

 

Enthusiast

Re: Partition Elimination

Thank you so much for your quick repsone.

 

I will try this and update.

 

Thanks.

Enthusiast

Re: Partition Elimination

Hi Dnoeth,

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.

 

Thanks.