Re: Partitioning Advice

Thank you for your feedback.  I was trying to avoid having to add an additional 
column to the table.

Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> wrote:I don't know if you could 
use a function within the
'partition by range' clause.

You may try (if you're on 9i) - Here TRANSACTION_DAY
is populated from TRANSACTION_DATE, say, by a trigger:

SQL> create table AUDIT_LOG
2 (
3 TRANSACTION_DATE DATE,
4 TRANSACTION_DAY CHAR(3)
5 )
6 PARTITION BY LIST (TRANSACTION_DAY)
7 (
8 PARTITION P1 VALUES ( 'MON' ),
9 PARTITION P2 VALUES ( 'TUE' ),
10 PARTITION P3 VALUES ( 'WED' ),
11 PARTITION P4 VALUES ( 'THR' ),
12 PARTITION P5 VALUES ( 'FRI' ),
13 PARTITION P6 VALUES ( 'SAT' ),
14 PARTITION P7 VALUES ( 'SUN' )
15 )
16 /

Table created.



--- d cheng wrote:

> Hi listers,
> 
> I have the following audit table for which I am
> trying to come up with a partitioning strategy so I
> can keep 7 days worth of data.
> 
> create table AUDIT_LOG
> ( TRANSACTION_DATE DATE,
> USERID NUMBER,
> OPCODE VARCHAR2(3),
> MSGTEXT VARCHAR2(255));
> 
> I tried the below but have not been successful with
> it. Are there other approaches to accomplish my
> goal?
> 
> PARTITION BY RANGE(to_char(TRANSACTION_DATE,'D'))
> (PARTITION P1 VALUES LESS THAN ('2')),
> PARTITION P2 VALUES LESS THAN ('3')),
> PARTITION P3 VALUES LESS THAN ('4')),
> PARTITION P4 VALUES LESS THAN ('5')),
> PARTITION P5 VALUES LESS THAN ('6')),
> PARTITION P6 VALUES LESS THAN ('7')),
> PARTITION P7 VALUES LESS THAN ('8')
> )
> 
> Thank you in advance for your help!
> 
> - David
> 
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam
> protection around 
> http://mail.yahoo.com 


__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 


                
---------------------------------
 Start your day with Yahoo! - make it your home page 

Other related posts: