A little while ago, I received the following question by email:
What is the optimum level of partitioning in Oracle 10g, as in what is an efficient number of partitions that you can have in a single table? What are the advantages and disadvantages of having more than 3,000 partitions in a table?
These are actually questions I addressed seven years ago in my first book. The answers you might come up with for any specific occasion may vary, but the analysis does not change.
Start by asking why you need partitioning. What are you expecting to do with the partitioned tables? What benefits do you expect to see?
Are you thinking of range, list, hash, or composite partitioning? Are you thinking of local and global indexes, or just local, or just global? Are you expecting to see partition elimination in queries, what about partition-wise joins? Do you expect your queries to access low volumes of data or high volumes of data? Are you loading data using real-time (OLTP) methods or batch (DSS) methods? Are you expecting to eliminate old data by dropping partitions? Who (or what) is writing the SQL that hits the partitioned tables? What will the impact be in “scenario X” if it doesn’t work properly?
Moving from 8i to 10g, there are only three significant changes in partitioning technology:
- The optimizer is better at identifying options for partition elimination and partition-wise joins. But that doesn’t stop it from being baffled by the bizarre SQL that comes from generic “point and click” tools.
- There is an option for maintaining global indexes reasonably efficiently when you do partition maintenance such as dropping a partition.
- Oracle no longer renumbers every single (higher) partition individually when you drop a partition of a partitioned table.
The last feature is the one that makes it reasonable to use larger numbers of partitions for a single table in 10g than you would in 8i. So (assuming I had done all the other analysis properly) I probably wouldn’t worry too much about having 3,000 partitions in a table with a dozen indexes in 10g, when I used to start worrying in 8i after I reached about 1,000 and only three or four indexes.