I have a Large Fact table paritioned by date(daily date) and i have another smalldimension table "dates" which has coulmns current,current_month_end,previous_month_end so on..in which flag is set to "Y" for the corresponding date other dates is set to "N".
When i join both tables to get data for current data,teradata is doing all rows scan.But i want only particular partition to be processed.
My SQL is like:
sel * from Fact_Table F ,Dimension_Table D where F.date=D.date and D.current='y'.
How can i take advantage of partiton elimination even when i joining this dimension table to get data for particular date.
Thanks in Advance!!!
Try like .The below query might work in TD 13
select * from Fact_Table F
where (select D.date from Dimension_Table D where D.current='Y' ) = F.date.