Oracle DB daily partitioning

Question

I have the following table

  CREATE TABLE "METRIC_VALUE_RAW" 
   (    
    "SUBELEMENT_ID" INTEGER NOT NULL , 
    "METRIC_METADATA_ID" INTEGER NOT NULL , 
    "METRIC_VALUE_INT" INTEGER, 
    "METRIC_VALUE_FLOAT" FLOAT(126), 
    "TIME_STAMP" TIMESTAMP  NOT NULL 
   ) ;

1. Every hour data will be loaded into the table using sql loader.
2. I want to create partitions so that data for every day go into a partition.
3. In table I want to store data for 30 days. So when it crosses 30 days, the oldest partition should get deleted.

Can you share your ideas on how I can design the partitions.

Answer 1

here is an example how to do it on Oracle 11g and it works very well. I haven't tried it on Oracle 10g, you can try it.

This is the way, how to create a table with daily partitions:

CREATE TABLE XXX (
    partition_date   DATE,
  ...,
  ...,
)
PARTITION BY RANGE (partition_date)
INTERVAL (NUMTODSINTERVAL(1, 'day'))
(
   PARTITION part_01 values LESS THAN (TO_DATE('2000-01-01','YYYY-MM-DD'))
)
TABLESPACE  MY_TABLESPACE
NOLOGGING;

As you see above, Oracle will automaticaly create separate partitions for each distinct partition_day after 1st January 2000. The records, whose partition_date is older than this date, will be stored in partition called 'part_01'.

You can monitore your table partitions using this statement:

SELECT * FROM user_tab_partitions WHERE table_name = 'XXX';

Afterwards, when you would like to delete some partitions, use following command:

ALTER TABLE XXX DROP PARTITION AAAAAA UPDATE GLOBAL INDEXES

where 'AAAAAA' is parition name.

I hope it will help you!

Answer 2

As i said , There are big differences in partition automation between 10g and 11g. In 10G you will have to manually manage the partitions during your ETL process (I'm sure every 10g DBA has a utility package he wrote to manage partitions ... ).

For steps 1 & 2 , you have several options

1. load data directly into the daily partition.
2. load data into a new partition and merge it into the daily one.
3. load data into a new partition every hour, and during a maintenance window merge all hourly partitions into a daily partition.

The right way for you depends on your needs. Is the newly added data is queried immediately ? In what manner ? Would you query for data across several hours (or loads...) ? Are you showing aggregations ? are you performing DML operations on the data (DDL operations on partitions cause massive locking).

about 3, again - manually. drop old partitions.

In 11G, you have the new interval partition feature with automates some of the tasks mentioned above.

Answer 3

Following is a sample create table sql to parititon data:

CREATE TABLE quarterly_report_status (
    report_id INT NOT NULL,
    report_status VARCHAR(20) NOT NULL,
    report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)
PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
    PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00') ),
    PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-02 00:00:00') ),
    PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-03 00:00:00') ),
    PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-04 00:00:00') ),
    PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-05 00:00:00') ),
    PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-06 00:00:00') ),
    PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-07 00:00:00') ),
    PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-08 00:00:00') ),
    PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-09 00:00:00') ),
    PARTITION p9 VALUES LESS THAN (MAXVALUE)
);

Paritions will be created by DBa and rest will be taken care of by oracle. If you want to delete partition then you will have to write separate jobs for it.