Re: multi column range partitioning

The comparison logic is like this:
You compare the first key, if
1. Value < first partition key of 1st partiton -> insert into 1st partition
2. Value = first partition key of 1st partiton -> move on to the 2nd key and compare using same logic.
3. Value > first partition key of 1st partiton -> move on to the 2nd partition and compare using the same logic.

In your case, as soon as ORACLE realizes WAVE_ID 5029 < 5030, it immediately stops comparing and puts it into the first partition. It does not matter what value prob_type is at this point. Partition P5029_4 and P5031_4 will be able to store precisely 1 record - (5030, 4) and (5032, 4) respectively. This does not make much sense.

• Jusung Yang

cosmini_at_yahoo.com (cosmin ioan) wrote in message news:<80fb0771.0303281643.4b32f2e3_at_posting.google.com>...
> hello all,
> I have a multi column range partition as in the example below and I'm
> not understanding why when I insert data, it is placed in what appears
> to be the incorrect partition/tablespace.
>
> thx much,
> Cosmin
>
> CREATE TABLE test_part
> (
> WAVE_ID NUMBER(10) NOT NULL,
> PROB_TYPE NUMBER(1) NOT NULL)
> PARTITION BY RANGE (WAVE_ID, PROB_TYPE)
> (
> PARTITION P5029_3 VALUES LESS THAN (5030, 4)
> nologging
> TABLESPACE P5029_3
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> BUFFER_POOL DEFAULT
> ),
> PARTITION P5029_4 VALUES LESS THAN (5030, 5)
> NOLOGGING
> TABLESPACE P5029_4
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> BUFFER_POOL DEFAULT
> ),
> PARTITION P5031_3 VALUES LESS THAN (5032, 4)
> NOLOGGING
> TABLESPACE P5031_3
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> BUFFER_POOL DEFAULT
> ),
> PARTITION P5031_4 VALUES LESS THAN (5032, 5)
> NOLOGGING
> TABLESPACE P5031_4
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> BUFFER_POOL DEFAULT
> ),
> PARTITION P5033_3 VALUES LESS THAN (MAXVALUE, MAXVALUE)
> NOLOGGING
> TABLESPACE MS
> PCTFREE 10
> INITRANS 1
> MAXTRANS 255
> STORAGE (
> INITIAL 64K
> MINEXTENTS 1
> MAXEXTENTS 2147483645
> BUFFER_POOL DEFAULT
> )
> )
> NOCACHE
> NOPARALLEL;
>
> insert into test_part values (5029, 3);
>
> insert into test_part values (5029, 4); --puts data in tablespace
> P5029_3!!!;
>
> insert into test_part P5029_4 values (5029, 4); --also puts data in
> tablespace P5029_3 !!!
Received on Sat Mar 29 2003 - 00:29:56 CST