Re: multi column range partitioning

cosmin ioan wrote:
>
> 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

You could experiment with composite partitioning, where you range partition on the first column and then hash on the second. You do need some vigourous testing though - because you do end up with a whole lot of segments this way

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"