We want to partition some tables using interval partitioning on the creation date. Partitioning is for manageability - we want to drop older partitions eventually - and partition pruning for improving performance. What is the Recommended partition size for a table - I am referring to the bytes column in dba_segments? Based on that, I wanted to back calculate the interval using the data volume for each table.
If we use composite partitioning (interval-list) for some tables, how will the above answer change?
Thanks
The answer is.. "what is best for you" ie, your business requirements.
For example, if I worked in a sales organization where the dominant information request was "All sales since the start of the business week", I would probably look at partitioning by week, with each partition starting on a Monday. That serves the most important requirement.
But if I run real-time analytics on daily data for mainly today and yesterday, then I might partition by day, with a view to merging the data into larger partitions as the data ages.
One thing to keep in mind is that each partition is in effect a "table", ie, a segment on disk. So in the same way you probably would not like to manage a database with 5 million tables...you might not want to have a table that has 5 million partitions. That's a lot of dictionary objects that need to be looked after.
Not that it is a "rule", but generally I'd consider it rare to see partitions smaller than (say) a gigabyte in size.
For example, with the advent of online merge in 18c, it gives rise to potentially using a much more granular partitioning mechanism to avoid the need for indexes, knowing that you can merge them later without outage in order not to have silly amounts of partitions.
I blogged about that here
https://connor-mcdonald.com/2018/07/25/hyper-partitioned-index-avoidance-thingamajig/