0 - 5 of 5 tags for partition elimination

Translating descriptive dates, like last-week, with in line SQL code to achieve Partition elimination.

For many years now we have seen the optimizer NOT chose partition elimination for a query when that query qualifies on a date column that is the partitioning column, UNLESS we are qualifying on a hard coded date value like where columnname = '2013-03-26'.

Background:
We have a history table which is partitioned on loadDate. This table also has another column called ProcessYearMonthNumber which is the year and month number of the loadDate. I know this can be derived on the fly but for some reason we have this as a separate column with values populated by ETL.

Hello TD Masters,

Can you please help me understand why my query doesn't not pick a partition though the inner select returns a partitioning value.

I've attached the explain for the regular query and also attached the explain for hard coded value:

WHERE ship_dt IN ( '2012-06-11','2012-06-14')

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".