When defining a PPI on a date column, would it be better to define your partitions so that fewer (and larger) partitions are accessed by the query or would it be better to define your partitions so that more (but smaller) partitions are accessed?
Currently I have a PPI defined at the day level, but not a lot of queries request that level of detail. And queries that access a large range of time (multiple years in some cases), are taking longer and longer and we're not getting much advantage from DPE. I'm considering changing the partition to month-level but am not sure if we'll get any advantage by scanning fewer, bigger partitions.
I'll suggest you to go with queries retrieval patterns. If the commonly access range is years then it make sense for yearly partition. Between , you can also partition the same table with different ranges ( days, month ,years so on) but the idea is to acknowledge the basis of the range and the n applying partitions. Hope it helps.