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?

Answer 1

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.

Answer 2

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 3

Here is a sample of something I came up with for creating partitions using SYSDATE and an offset. I had to create replacement parameters with a string concatenated to the SYSDATE:

COLUMN temp_var new_value partition_name_01;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 3, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_01;
SELECT TO_CHAR(SYSDATE - 3, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_02;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 2, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_02;
SELECT TO_CHAR(SYSDATE - 2, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_03;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE - 1, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_03;
SELECT TO_CHAR(SYSDATE - 1, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
COLUMN temp_var new_value partition_name_04;
SELECT 'TABLE_NAME_' || TO_CHAR(SYSDATE, 'YYYYMMDD') AS temp_var FROM dual;
COLUMN temp_var new_value partition_date_04;
SELECT TO_CHAR(SYSDATE, 'SYYYY-MM-DD') || ' 00:00:00' AS temp_var FROM dual;
CREATE TABLE TABLE_NAME
(
   SEQ_NO                 NUMBER NOT NULL,
   INSERT_DATE            DATE NOT NULL,
   FIRST_NAME             VARCHAR2 (256 BYTE),
   LAST_NAME              VARCHAR2 (256 BYTE),
   ID_NUM                 NUMBER,
   ID_STATUS              NUMBER
)

PARTITION BY RANGE
   (INSERT_DATE)
   SUBPARTITION BY LIST
      (ID_STATUS)
      SUBPARTITION TEMPLATE (
         SUBPARTITION SP1 VALUES (0) TABLESPACE &tblspce,
         SUBPARTITION SP2 VALUES (1) TABLESPACE &tblspce,
         SUBPARTITION SP3 VALUES (2) TABLESPACE &tblspce)

   (
   PARTITION &partition_name_01
      VALUES LESS THAN
      (TO_DATE ('&partition_date_01',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')),

   PARTITION &partition_name_02
      VALUES LESS THAN
      (TO_DATE ('&partition_date_02',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')),

   PARTITION &partition_name_03
      VALUES LESS THAN
      (TO_DATE ('&partition_date_03',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')),
sysdate

   PARTITION &partition_name_04
      VALUES LESS THAN
      (TO_DATE ('&partition_date_04',
                   'SYYYY-MM-DD HH24:MI:SS',
                   'NLS_CALENDAR=GREGORIAN')))

ENABLE ROW MOVEMENT;