Partition Elimination

Database
Highlighted
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.

1 REPLY
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.