According to the following link, it is advisable to use hash partitioning when, "The sizes of range partitions would differ quite substantially or would be difficult to balance manually." This implies that when creating partitioned tables, the partitions should be relatively similar in size. Is this true?
Well, yes it's true. One important reason for partitioning is to reduce the I/O load for queries. That is, only one partition needs to be read for a given query.
If you make a bunch of assumptions, such as:
- An even distribution of rows over queries over time (relative to the partitioning key)
- Record sizes independent of the partitioning key
- Equal access for the partitions
Then you will discover that for "n" partitions the most efficient solution is when they are equal sized.
That said, there are definitely special cases. If the partitioning key is referenced through ranges (
between), then hash partitioning is not optimal. If some data is accessed much more frequently (such as the most recent data), then equal-sized partitions may not be suitable. If you are using partitions for fast deletes (by dropping the partition), then hash-based partitions are not suitable.
Although it does not seem so, Oracle is not that smart(yet). When it can not predict which partitions will be used, the exec plan will be the same for every partition visited (with some exceptions on 12c).
So when stats are very different for every partition and the SQL visits them all, then the performance might not be optimal.
Also the number of partitions should be reasonable, when you create a table having tens of thousands of partitions, then access via a local index might be much much slower and then you end up with partitioned table having global indexes only. In such a case the partition drop implies a maintenance of globally partitioned indexes, and such an operation might be very slow. So at the end you loose most of the advantages of partitioned tables.
For example when you partition orders by creation date, they might be equal in size, but most likely the old ones will be already processed, while the recent ones will not. So it will be questionable whether the index on "processed" flag will be used for a query iterating over all partitions. In similar cases do not be afraid of row movement and sub-partitioning.
The first choice of what type of partitioning should be what are your goals for partitioning. As Tom Kyte would say "PARTITIONING != FAST" -- although it can generally speed things up, particularly when queries would be selecting data from just a small fraction of the total rows, as Gordon mentioned in his answer.
If the primary goal is to easily archive old data, such as the typical
customer_order table, range partitioning would be used; if it is partitioned by month, you can drop partitions containing data over 5 years old very easily. You can also load the "last month" activity from a non-partitioned table to a partitioned history table.
If your data is location-specific, then list_partitioning would be one approach, where each country's data were in different partitions, assuming that common data access would be by country.
The Database Concepts lists the main benefits of partitioning as
- Increased availability
- Easier administraction
- Reduced contention
- Enhanced query performance
But again, you need to determine what makes sense, based on the business need, and be aware you can have composite partitioning - one level partitioned one way, and that level's partition data be also partitioned, using a different method.