Partition By Date 1

[THoey]

All,

I am working on a new requirement to build a very large table, 1 million records per day, 180 day retention.

To access these records quickly and efficiently, it was mentioned that the table should be partitioned by date. At the start of each day, a stored procedure would be run that would first drop the partition (and index) that has now become 181 days old. It would then create a new partition and index for the current day.

I thought I tried doing this a while back and could not get it to work. I was using OEM to create the partitioned table, and I don't think it would take a date as the partitioned field.

Anyone else have any luck with this? Anyone see any problems with this sort of design?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[stefanhei]

Hi.
There is no problem with a date as partition field:

CREATE TABLE t (a NUMBER,d DATE)
PARTITION BY RANGE (d)
(PARTITION old_data VALUES LESS THAN (TO_DATE('20030101','YYYYMMDD')),
PARTITION new_data VALUES LESS THAN (MAXVALUE))

Table created.

With a million records a day partitioning sounds like a good idea too. Just keep in mind, that a date also contains time when you create/drop the partitions.

Stefan

 

[THoey]

Thanks for the reply. As I mentioned, was trying this through OEM, so maybe it wasn't an option then (8i) and is okay now. My next question leads to the cycle of partitions where I am deleting the old partition and creating a new partition for the new days data. I gather this is something like:

ALTER TABLE DROP PARTITION 20040701;
ALTER TABLE ADD PARTITION 20041101_DAT VALUES LESS THAN (TO_DATE('20041102','YYYYMMDD'));
CREATE INDEX 20041101_IDX ON PARTITION 20041101_DAT;

I didn't look it up, but is that close? Also, what happens to the partitioned indexes when a partition is dropped? Are any of the others listed as unusable?

Thanks again...

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[stefanhei]

Sounds basically correct (didn't look it up neither). As far as I remember global indexes have to be rebuilt after a partition is dropped, local indexes should disappear with the partition (not sure about this one).

Stefan

 

[THoey]

That sounds familiar too. Thanks for for the advice, I will do the grunt work of trial and error, I mean looking it up.



Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...