Fewer Partitions vs Smaller Partitions

Database
Enthusiast

Fewer Partitions vs Smaller Partitions

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.

Thoughts?
Tags (2)
2 REPLIES
Enthusiast

Re: Fewer Partitions vs Smaller Partitions

It _mostly_ depends on issues, that wanted to be resolved, queries you executes, frequency of them and so on.

>>but not a lot of queries request that level of detail

Just collect time-statistic, how long executes the same queries on tables with different ppi, as first step...

ps: may be it's reasonable to optimize queries?
Enthusiast

Re: Fewer Partitions vs Smaller 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.