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