Re: Spam:range partition issue

From: Job Miller <jobmiller_at_yahoo.com>
Date: Wed, 14 Nov 2007 16:35:02 -0800 (PST)
Message-ID: <42240.52922.qm@web53901.mail.re2.yahoo.com>

11g introduced range- range composite partitioning, which will behave as you expected multi-column range partitioning to behave in 10g. In 10g, it wasn't range-range, it was multi-column range. big difference in terms of where the data ends up, as you found out.

Original Message ---- From: John D Parker <orclwzrd_at_yahoo.com> To: Harvinder.Singh_at_MetraTech.com; oracle-l_at_webthere.com; oracle-l_at_freelists.org Sent: Wednesday, November 14, 2007 4:38:52 PM Subject: Re: Spam:range partition issue

Strangely enough I was researching a similar issue for a problem we are having with an internal app. After reviewing Jonathan Lewis Practical 8i(still applies) and looking at page 17-5 in the database admin guide for 10g, I found the following article on line. http://oraclesponge.wordpress.com/2007/01/14/thoughts-on-range-range-composite-partitioning/

Hopefully it will be as helpful to you as it was to us. Two column partitioning it turns out is not like two dimentional arrays. We found all our data in the lowest matching partition when it should be scattered across several.

The info in the above resources was quite helpful. I'd explain it but they do a much better job. Now if I could just find the reference pages for ANSI SQL2 Vector Comparison...

John

Harvinder Singh <Harvinder.Singh_at_MetraTech.com> wrote:

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

Be a better pen pal.
Text or chat with friends inside Yahoo! Mail. See how.

Get easy, one-click access to your favorites. Make Yahoo! your homepage.
http://www.yahoo.com/r/hs

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

Received on Wed Nov 14 2007 - 18:35:02 CST