Re: multi column range partitioning

thx Connor,
so in effect, is there an elegant workaround to further spread the data whose first partition column is the same but the second partition key is different, other than simply make the first column unique and then be forced to do substr etc ("5029 || 3") in the query on the first column/key?

thx,
Cosmin

Connor McDonald <connor_mcdonald_at_yahoo.com> wrote in message news:<3E857D96.155F_at_yahoo.com>...
> cosmin ioan wrote:
> >
> > 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 !!!
>
> See http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> hth
> connor
Received on Sat Mar 29 2003 - 10:08:15 CST