Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Another partitioning question part 2

RE: Another partitioning question part 2

From: אדר יחיאל <adary_at_mehish.co.il>
Date: Tue, 19 Mar 2002 06:08:25 -0800
Message-ID: <F001.0042CF54.20020319060825@fatcity.com>


Hello John

Since all values starting with 'V' are lower then 'W' they will be inserted into partition p1. (tested it).
I did some tests but could not find a way to do what you want. Maybe a composite partitioning with 4 hash subpartitions will get you some results.

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Shaw John-P55297 [SMTP:P55297_at_motorola.com]
> Sent: Mon, March 18, 2002 5:41 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Another partitioning question part 2
>
> I tried the partition by range feature and didn't get the results I
> expected. It seemed to ignore the second key when inserting data.
> create table test_part(
> id number(11) ,
> owner_id number(11) not null,
> owner varchar2(30) not null,
> street varchar2(40))
> partition by range(owner,owner_id )
> (
> partition p5 values less than ('V',999999) tablespace test,
> partition p1 values less than ('W',2000) tablespace test,
> partition p2 values less than ('W',5000) tablespace test,
> partition p3 values less than ('W',10000) tablespace test,
> partition p4 values less than ('W',50000) tablespace test,
> partition p6 values less than ('W',99999) tablespace test,
> partition p7 values less than (maxvalue,99999) tablespace test
> );
> What I am trying to accomplish is to get all values less than 'V' into one
> partion , all values = 'V' into 5 partitions by numeric range , and all
> owner values greater that 'V' into the last partition.
> It is sorting correctly one the first value 'owner' but is ignoring the
> second range value when inserting records into the table, all owner_id
> reanges are just going into the first partition. Anybody tell me what I
> did
> wrong - or maybe the book and chapter.
> 8.1.6 on NT
>
> -----Original Message-----
> Sent: Friday, March 15, 2002 2:48 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Kirti - If I can humbly beg to differ. I assumed that it would work the
> way
> you described. However, I conducted some tests and found to my surprise
> that
> it seems to base decisions on the right-most column (although I only
> tested
> two columns). Therefore, to use your examples,
>
> P1 -- values less than ('X', 999999) will contain values where column1 =
> 'X'
> and column2 less than 999999
> P2 -- values less than ('Y', 999999) will contain values where column1 =
> 'Y'
> and column2 less than 999999.
>
> Since my column1 only has two values, I didn't test what it does with
> "unlimited" in the first column.
> I'm not saying that didn't miss something. My ulterior motive for
> responding on the list is that I frequently get some fuzzy ideas clarified
> by others on the list.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Friday, March 15, 2002 1:33 PM
> To: Multiple recipients of list ORACLE-L
>
>
> If I understood the original question correctly, with the given conditions
> there will be three partitions. Oracle evaluates concatenated key from
> left
> to right order, so if the type and seq number are the partitioning columns
> here then the partitions would be:
> P1 -- values less than ('X', 999999) This will contain everything where
> type
> < X
> P2 -- values less then ('Y', 999999) This will contain everything where
> type
> = X
> P3 -- values less then (MAXVALUE, MAXVALUE) This will contain everything
> else. I am using 999999 to denote the highest value for the seq number.
>
> Any other ideas?
>
> - Kirti
>
>
>
> -----Original Message-----
> Sent: Friday, March 15, 2002 11:28 AM
> To: Multiple recipients of list ORACLE-L
>
>
> John - At last a question I can answer! Anyway I think so.
> You can partition on a concatenated key. I just did this on our data
> warehouse and brought query times from over 2 minutes to under 10 seconds.
> Here is what my partition looks like.
>
> create table sumacctfact2
> nologging
> pctfree 5
> partition by range ( periodgrain, periodenddate )
> (
> partition sum_fy_01 values less than ('FY',
> to_date('01011999','mmddyyyy'))
> tablespace data_fy_01
> storage ( maxextents unlimited ),
> partition sum_fy_02 values less than ('FY',
> to_date('01012000','mmddyyyy'))
> tablespace data_fy_02
> storage ( maxextents unlimited ),
> partition sum_fy_03 values less than ('FY',
> to_date('01012001','mmddyyyy'))
> tablespace data_fy_03
> storage ( maxextents unlimited ),
> partition sum_fy_04 values less than ('FY',
> to_date('02012001','mmddyyyy'))
> tablespace data_fy_04
> storage ( maxextents unlimited ),
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
> -----Original Message-----
> Sent: Friday, March 15, 2002 10:38 AM
> To: Multiple recipients of list ORACLE-L
>
>
> 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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: Deshpande, Kirti
> INET: kirti.deshpande_at_verizon.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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: 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: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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).
Received on Tue Mar 19 2002 - 08:08:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US