Dynamic table partitioning in Oracle
I'm in the process of building a database storage for my app consisting on a single table with a huge data volume (hundreds of millions of records). I'm planning on having an index on the date field, since I'll be doing a batch recovery of all the records in a given period of time every now and then (for example, retrieving all records for the following day, at midnight).
Since the number of records is huge and performance is an important concern in this system, I would like to know if there is a way I can dynamically partition my table so that I can retrieve the records faster, creating and truncating partitions as they are no longer needed. For example, how would I go about creating a partition for the following day and populating it with the rest of the data after I'm done processing today's records?
Answer
In 11g we can define INTERVAL partitions, and Oracle will automatically create new partitions when it gets new records whose keys don't fit in any of the existing ranges. This is a very cool feature. Find out more.
One thing to bear in mind is that Partitioning is a chargeable extra on top of the Enterprise Edition license. So it is not cheap to use.