RE: Spam:range partition issue

From: Ric Van Dyke <ric.van.dyke_at_hotsos.com>
Date: Thu, 15 Nov 2007 10:30:52 -0600
Message-ID: <C970F08BBE1E164AA8063E01502A71CFDA2AF2@WIN02.hotsos.com>

Roger that. I was a bit too quick in my response.

Here is a test that shows what I was trying (but messed up) to say yesterday:

SQL> r
  1 CREATE TABLE test_part
  2 (
  3 Col1 NUMBER(10) NOT NULL,
  4 Col2 NUMBER(10) NOT NULL
  5 )
  6 PARTITION BY RANGE (COL1, COL2)
  7 (
  8 PARTITION USAGE_1 VALUES LESS THAN (897515520, 1500000),   9 PARTITION USAGE_2 VALUES LESS THAN (897515520, 3500000) 10* )

SQL> insert into test_part values(897515520,1200000);
SQL> insert into test_part values(897515520,1600000);
SQL>
SQL> select count(*) from test_part partition(usage_1);

       COUNT(*)
---------------
              1

SQL> select count(*) from test_part partition(usage_2);

COUNT(*)

1
SQL>
*********** Data is in the partitions as expected.

SQL>
SQL> insert into test_part values(897515519,1600000); *********** This insert "should" be in the second partition.

SQL> insert into test_part values(897515519,1200000); *********** This insert should be in the first partition.

SQL> select count(*) from test_part partition(usage_1);

COUNT(*)

Both are really in the first.

SQL> select count(*) from test_part partition(usage_2);

COUNT(*)

1
SQL>
SQL> select * from test_part partition(usage_1);

COL1 COL2
--------------- ---------------

      897515520         1200000
      897515519         1600000
      897515519         1200000

-----------------------

Ric Van Dyke
Hotsos Enterprises
Cell 248-705-0624

-----Original Message-----
From: Bob Carlin [mailto:smeghead.rimmer_at_gmail.com] Sent: Thursday, November 15, 2007 11:21 AM To: Ric Van Dyke
Cc: Harvinder.Singh_at_MetraTech.com; oracle-l_at_webthere.com; oracle-l_at_freelists.org
Subject: Re: Spam:range partition issue

Ric,
Actually is works by comparing until is does *not* have a match. I tested Harvinder's example, and it behaves as expected; I think Harvinder must have had an error in his own test.

Quoting from the manual:

"When a table or index is partitioned by range on multiple columns, each

partition bound and partitioning key is a list (or vector) of values. The partition bounds and keys are ordered according to ANSI SQL2 vector comparison rules. This is also the way Oracle orders multicolumn index keys.

To compare a partitioning key with a partition bound, you compare the values of their corresponding columns until you find an unequal pair and

then that pair determines which vector is greater. The values of any remaining columns have no effect on the comparison."

Ric Van Dyke wrote:
>
> 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 Thu Nov 15 2007 - 10:30:52 CST