Re: Partition table by date field

From: Steve Blomeley <steveblomeley_at_yahoo.co.uk>
Date: 2000/06/16
Message-ID: <3949683B.B23BBD29@yahoo.co.uk>#1/1

ken k wrote:
>
> Has anyone done this? What is the syntax for entering the date?
>
> create table
> ...
> partition by range(date_field)
> (partition p1 value less then (????)
> )
>
> Or words to that effect.
>
> Thanks,
>
> Ken

The following partition clause worked OK for me ... Note that if you omit the MAXVALUE partition, then any rows which don't fit into any other partition will be "rejected" (ie: they will not be inserted into the table)

CREATE TABLE mytab
(

   ...
   mydate DATE NOT NULL,
   ...
)
PARTITION BY RANGE (mydate)
(

PARTITION before_2k

      VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      TABLESPACE users1,
   PARTITION in_2k     
      VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY'))
      TABLESPACE users2,
   PARTITION after_2k  
      VALUES LESS THAN (MAXVALUE)
      TABLESPACE users3

)

Rgrds
Steve Received on Fri Jun 16 2000 - 00:00:00 CDT