| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Paritioning Query
Quick recap
Trying to set up a partitioned table using the date field as the partition.
Want to store 12 months worth of data in the partitions - JAN_AUD, FEB_AUD,
.... , DEC_AUD
On the 28th of the month drop the next months AUD partition and recreate it,
with a
new date partition.
Originally wanted to do VALUES LESS THAN (TO_DATE('01-FEB','DD-MON')), but
since Y2K
this is not possible.
Asked for other solutions apart from splitting into 3 different fields.
Sybrand Bakker responded with the following:
>
> Apparently your main problem is you don't want to type the year in
> your partition definition. And just because of this you consider the
> date field into 3 separate values. That would be the most excessive
> measure I ever saw to avoid typing the year.
> The 'other ideas' you request are quite obvious: if you want one
> partition per month you have to take the year into account.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
However, this answer, obviously, does not help me.
Most of the Oracle examples on using dates in partitions so the fields as
PARTITION BY RANGE (Year, Month, Day)
( PARTITION FIRST VALUES LESS THAN ('2004','12','01') )
Hence the request NOT to use this method and to get other suggested methods.
I also put this message on the Metalink Database EE forum, with the
following result
Victor Pankratov said
I would make 13 partitions and name the last partition TRAIL_AUD as it has
no upper bound. Having it this way, you can simply drop the FEB_AUD
partition and then SPLIT PARTITION TRAIL_AUD AT
(TO_DATE('01-MAR-2005','DD-MON-YYYY')) INTO (PARTITION FEB_AUD, PARTITION
TRAIL_AUD) /* PARALLEL n -- to speed up the split */. This way, FEB_AUD
partition will get ahead of the rest of partitions that will still contain
2004 data.
By the way, it is worth noting that you improperly use TO_CHAR() where
TO_DATE() is appropriate. Oracle knows how to compare dates and sort rows
into partitions accordingly, so you should always use dates for dates.
This WORKED a treat. Created my initial table with 2003 values in all
fields, then executed
ALTER TABLE AUDIT_LOG DROP PARTITION JAN_AUD;
ALTER TABLE AUDIT_LOG SPLIT PARTITION TRAIL_AUD AT
(TO_DATE('01-FEB-2004','DD-MON-YYYY'))
INTO (PARTITION JAN_AUD TABLESPACE AUDIT_Q1, PARTITION TRAIL_AUD);
for all partitions up until the current month.
Imported the 'test' table into the partitioned table and the results were
great.
Will now implement into the 'TEST' environment.
Thank you Victor - hope someone else with this problem gets help from this answer.
Cheers
Craig.
 |
 |