Re: Partitioning Advice
- From: d cheng <dc4oracle@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Wed, 3 Aug 2005 09:59:23 -0700 (PDT)
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:
