RE: Spam:range partition issue

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Wed, 14 Nov 2007 15:17:40 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CFDA2A52@WIN02.hotsos.com>

Oracle compares from left to right and stops when it has a match:  

First row: 897515520,1200000 The first value is less then or equal to the first key of the partition, so it goes in that partition.  

Second row: 897515520,1600000 The first value is less then or equal to the first key of the partition, so it goes in that partition.  

In both cases it never even looks at the second value. I forget the name of this mechanism but it's the way it works.  

---

Ric Van Dyke

Hotsos Enterprises

---

 

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Harvinder Singh Sent: Wednesday, November 14, 2007 3:37 PM To: oracle-l_at_webthere.com; oracle-l_at_freelists.org Subject: Spam:range partition issue  

Hi,  

I am creating range partitioning on 2 columns as following:  

CREATE TABLE test_part

(

Col1             NUMBER(10)                 NOT NULL,

Col2  NUMBER(10)                 NOT NULL

)

TABLESPACE tab_1

PARTITION BY RANGE (ID_USAGE_INTERVAL, ID_ACC) (

  PARTITION USAGE_1 VALUES LESS THAN (897515520, 1500000)     TABLESPACE tab_2,

  PARTITION USAGE_2 VALUES LESS THAN (897515520, 3500000)     TABLESPACE tab_3

)
 

insert into test_part values(897515520,1200000)

insert into test_part values(897515520,1600000)

commit  

select count(*) from test_part partition(usage_1)  

select count(*) from test_part partition(usage_2)  

I expect both the partitions usage1 and usage2 to have 1 row each but both the rows are going to usage1, What can be the possible issue?  

Thanks

--Harvinder  

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Nov 14 2007 - 15:17:40 CST