Re: Partitioning Advice

From: d cheng <dc4oracle_at_yahoo.com>
Date: Wed, 3 Aug 2005 09:59:23 -0700 (PDT)
Message-ID: <20050803165923.5768.qmail@web34006.mail.mud.yahoo.com>

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

Deepak Sharma <sharmakdeep_oracle_at_yahoo.com> 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

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

Received on Wed Aug 03 2005 - 12:01:24 CDT