Partition Elimination Puzzel

Database
Enthusiast

Partition Elimination Puzzel

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.

The user base can run  queries by applying filters on loadDate or ProcessYearMonthNumber.Obviously, the queries with filter on loadDate are running faster because of the partition elimination. But queries on ProcessYearMonthNumber are running long because they are going for FullTableScans. Please note that the history tables are wide and large. So, there is lot of IO Wait time to do the FTS.  So, partition elmination is very important for us.

Question:

Is there away to eliminate the un-needed partitions when data filters are applied on ProcessYearMonthNumber? We are OK with deriving the ProcessYearMonthNumber from the table  in the view but we can not remove it altogether.

ProcessYearMonthNumber = Year(loadDate)*100+month(loadDate)

Optimizer is going for Full Table Scans when there is an expression on loadDate. So, even the simple query like where year(loadDate)=2012 is also going for FTS.

Any ideas to resolve the issue are highly appreciated.

Thanks in advance!!

Sateesh Polisetti