| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Another partitioning question
Kirti - Thanks so much for your reply and taking the time to work out an
example. I learned a couple of new techniques from your example. That one is
going in my files.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Friday, March 15, 2002 6:53 PM
To: Multiple recipients of list ORACLE-L
Hi Dennis,
Yes, your are right.
Thanks for catching it.
I messed up.
However, the order is still left to right...
While deciding on the partition when composite partition key is involved, the partition is selected as follows (for 2 column composite key):
Here is what I did:
SQL> create table t1 (c varchar2(1), n number(4)) 2 partition by range (c, n)
3 ( partition p1 values less than ('X', 400),
4 partition p2 values less than ('Y', 800),
5 partition p3 values less than (maxvalue, maxvalue)
6 );
Table created.
SQL> insert into t1 values ('A', 900);
1 row created.
SQL> insert into t1 values ('X', 900);
1 row created.
SQL> insert into t1 values ('X', 100);
1 row created.
SQL> insert into t1 values ('Y', 900);
1 row created.
SQL> insert into t1 values ('Y', 500);
1 row created.
SQL> insert into t1 values ('Z', 1000);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t1 partition (p1);
C N
- ----------
A 900 X 100
SQL> select * from t1 partition (p2);
C N
- ----------
X 900 Y 500
SQL> select * from t1 partition (p3);
C N
- ----------
Y 900 Z 1000
Unless I am still confused :(
Thanks.
-----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?
-----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 -- 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 ListsReceived on Mon Mar 18 2002 - 10:24:36 CST
--------------------------------------------------------------------
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).
 |
 |