Re: Another partitioning question

If your requirement is very strictly limited, so that a) you want to have the flag column ('X') as the first column of the partition

b) the flag is strictly a single character

c) you are using a character set where 'W' sorts immediately before 'X'

then the best you can do to meet the requirements you describe has two partitions for the not-X values and as many as you like for the X values using:

partition Pre_X values less than ('W',maxvalue), partition PX_100 values less than ('X', 100), partition PX_200 values less than ('X', 200),

    ...
partition PX_max values less than ('X', maxvalue), partition post_X values less than (maxvalue, maxvalue)

There are several drawbacks to using this strategy though, and I would start by questioning what you hopes to achieve through partitioning and investigate whether this notionally correct solution is going to do what he hopes it would anyway.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 15 March 2002 17:32

|I think what my boss is asking me to do is not possible, but since I
don't
|have much experience with partitioning I thought I'd ask here (I did
read
|some of manuals but didn't find an answer that suited my conditions).
My
|boss wants a table partitioned by 2 columns - seq_no and type. If the
type =
|'X' then it's just a range partition, but then he wants another
partition
|that contains all data that type!='X' but is inclusive of the entire
range.
|Is this possible?
|Something like (I know this syntax isn't correct )
|create table test_part(
|id number(11) unique,
|owner_id number(11) not null,
|type varchar2(30) not null,
|name varchar2(40))
|partition by range(owner_id,type)
|(partition p1 values less than (20000000) and type ='X' tablespace
test,
|partition p2 values less than (50000000) and owner_table ='X'
tablespace
|test,
|partition p3 values less than (100000000) and owner_table ='X'
tablespace
|test,
|partition p4 values less than (500000000) and owner_table ='X'
tablespace
|test,
|partition p5 values less than (1000000000) and owner_table ='X'
tablespace
|test)
|partition p6 values less that (1000000000) and owner_table !='X'
tablespace
|test;
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Shaw John-P55297
| INET: P55297_at_motorola.com
|
|Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
|San Diego, California -- Public Internet access / Mailing
Lists
|--------------------------------------------------------------------
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from). You may
|also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).