Daily Oracle Database 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 ) ; 

• Every hour, the data will be loaded into the table using the sql loader.
• I want to create partitions so that the data for each day goes to the partition.
• In the table, I want to store data for 30 days. Therefore, when it crosses 30 days, the oldest partition should be deleted.

Can you share your ideas on how I can create partitions.

Answer 1

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

For steps 1 and 2 you have several options

• upload data directly to the daily section.
• upload data to a new section and combine them into a daily one.
• upload data to a new section every hour and during maintenance window combines all hourly sections into a daily section.

The right way for you depends on your needs. Are recently added data requested immediately? How? Are you requesting data in a few hours (or downloads ...)? Do you show clusters? you perform DML operations on the data (DDL operations on partitions cause a massive lock).

about 3, again - manually. discard old sections.

In 11G, you have a new interval separation function with automation of some of the tasks mentioned above.

Answer 2

The following is an example of creating a sql table for 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) ); 

Passwords will be created by DBa, and rest will be based on oracle. If you want to delete a section, you will have to write separate tasks for it.

Answer 3

here is an example of how to do this on Oracle 11g and it works very well. I have not tried this on Oracle 10g, you can try.

Here's how to create a table with daily sections:

 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 automatically create separate partitions for each individual_day partition after January 1, 2000. Entries whose partition_size is older than this date will be stored in the "part_01" section.

You can track table sections with this statement:

 SELECT * FROM user_tab_partitions WHERE table_name = 'XXX'; 

Then, when you want to delete some partitions, use the following command:

 ALTER TABLE XXX DROP PARTITION AAAAAA UPDATE GLOBAL INDEXES 

where "AAAAAA" is the name of the parry.

Hope this helps you!