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.
Any ideas to resolve the issue are highly appreciated.
Thanks in advance!!