Re: Partitioning Advice

From: d cheng <dc4oracle_at_yahoo.com>
Date: Wed, 3 Aug 2005 10:05:36 -0700 (PDT)
Message-ID: <20050803170536.9826.qmail@web34010.mail.mud.yahoo.com>

Thank you for your suggestions. It appears functions are not permitted within the 'partition by range' clause.

Deepak Sharma <sharmakdeep_oracle_at_yahoo.com> wrote:Or (same logic):

SQL> create table AUDIT_LOG
2 (
3 TRANSACTION_DATE DATE,
4 TRANSACTION_DAY NUMBER
5 )
6 PARTITION BY RANGE (TRANSACTION_DAY)
7 (
8 PARTITION P1 VALUES LESS THAN (2),
9 PARTITION P2 VALUES LESS THAN (3),

10 PARTITION P3 VALUES LESS THAN (4),
11 PARTITION P4 VALUES LESS THAN (5),
12 PARTITION P5 VALUES LESS THAN (6),
13 PARTITION P6 VALUES LESS THAN (7),
14 PARTITION P7 VALUES LESS THAN (8)

15 )
16 /

Table created.

Deepak Sharma 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
> --
> http://www.freelists.org/webpage/oracle-l
>

Start your day with Yahoo! - make it your home page http://www.yahoo.com/r/hs

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


		
---------------------------------
 Start your day with Yahoo! - make it your home page 
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Aug 03 2005 - 12:07:47 CDT