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.