Re: multi column range partitioning

thanks much Jusung,
I think I took the 'less than' literally but instead it looks like the syntax should read 'less than or equal to'. In that case, your explanation makes perfect sense.

thanks again,
Cosmin

JusungYang_at_yahoo.com (Jusung Yang) wrote in message news:<130ba93a.0303282229.13bbb989_at_posting.google.com>...
> 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 - 07:18:21 CST