Re: Partitionned tables

Laurent,

Another way of doing what you want is to add a dummy column to the partitioning definition. In your example, it would look like:

CREATE TABLE my_table
(id number, type varchar2(10))
PARTITION BY RANGE (type, id)
(

    PARTITION p_value1 VALUES LESS THAN ( 'VALUE1', MAXVALUE )   , PARTITION p_value2 VALUES LESS THAN ( 'VALUE2', MAXVALUE ) ) ;

Before you insert VALUE3 data, you will need to:

ALTER TABLE my_table

   ADD PARTITION p_value3 VALUES LESS THAN ( 'VALUE3', MAXVALUE ) ;

This approach puts VALUE1 data in partition p_value1, VALUE2 data in partition p_value2, etc. If you need to insert a new partition between 2 existing partitions, then you need to ALTER TABLE SPLIT PARTITION. There are some tricks to optimizing this, posted on Jonathan Lewis' website (http://www.jlcomp.demon.co.uk).

HTH Richard Sutherland
rvsutherland_at_yahoo.com

<laurent.citton_at_socgen.com> wrote in message news:3a9b7a0b.521975410_at_192.168.48.70...
>
> Hi all,
>
> I was previously using partition views on Oracle 7.3 and I am
> currently migrating to Oracle 8i.
>
> Oracle doc says that partition views should not be used anymore, but
> partitionned tables should be used instead.
>
> However, my partitions were based on a single column, each distinct
> value of the column giving a partition. I have a limited number of
> values for this column and therefore a limited number of partitions.
> Inserting a row into this table is not possible if the partition has
> not been definied first.
>
> The problem is that partitionned tables are based on range of values,
> not on single values.
>
> In O7 it looked like
>
> create table t1 (id number, type varchar2(10));
> alter table t1 add constraint c1 check (type='VALUE1');
> create table t2 (id number, type varchar2(10));
> alter table t2 add constraint c2 check (type='VALUE2');
>
> create view v as
> select id, type from t1
> union all
> select id, type from t2;
>
> How can I create a partition table whose ranges are limited to a
> single value ? I do not want to use hash partitions, because I need to
> have a direct access to partitions data.
>
> Thanks in advance for your answers.
>
> Laurent
>
>
Received on Sat Mar 03 2001 - 07:49:35 CST