Re: Partitioning Advice

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Tue, 2 Aug 2005 15:33:30 -0700 (PDT)
Message-ID: <20050802223330.80231.qmail@web52808.mail.yahoo.com>

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 <dc4oracle_at_yahoo.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 02 2005 - 17:35:26 CDT