Dynamic table partitioning in Oracle

Question

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?

Thanks in advance! =)

Answer 1

Hi Eduardo,

you can automate the process of creating or truncating partitions through the use of dynamic SQL. You would write procedures with either EXECUTE IMMEDIATE or DBMS_SQL and you would schedule them with DBMS_JOB or DBMS_SCHEDULER (DBMS_SCHEDULER is a 10g feature and is more versatile than DBMS_JOB).

You probably want to build the partition statements manually first and automate the process later when you are confident with the DDL. You will find all the synthax in the documentation for the ALTER TABLE statement.

Answer 2

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.