Re: Multi Partition Types

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 06 Nov 2008 10:42:19 -0800
Message-ID: <1225996921.706868@bubbleator.drizzle.com>

atif wrote:
> On Nov 4, 6:16 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>

>> http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/partit...

> Thanks Powell
>
> I did some exercise and found some results which are confusing me
> I partitioned my table by Range
>
> CREATE TABLE Orders (
> RegionID NUMBER(4,0),
> Date DATE,
> ProductCategory NUMBER(4,0)
> )
> PARTITION BY RANGE(Date, RegionID, ProductCategory)
> (
> PARTITION r1 VALUES LESS THAN (To_Date('01/JAN/2009','DD/MON/YYYY'),
> 1000, 1000),
> PARTITION r11 VALUES LESS THAN (To_Date('01/JAN/2009','DD/MON/YYYY'),
> 5000, 5000),
> PARTITION r2 VALUES LESS THAN (To_Date('01/JAN/2010','DD/MON/YYYY'),
> 5000, 5000),
> PARTITION r3 VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE )
> )
> And inserted values as
>
> insert into Orders VALUES ('01/JAN/2008',2, 1)
> insert into Orders VALUES ('01/JAN/2008',4000, 1)
> insert into Orders VALUES ('01/JAN/2008',4000, 8000)
> insert into Orders VALUES ('01/JAN/2011',2500, 1)
> insert into Orders VALUES ('01/JAN/2019',3000, 6000)
> insert into Orders VALUES ('01/JAN/2019',1, 1)
>
> and found that
>
> row 1 went into r1
> row 2 went into r1
> row 3 went into r1
> row 4 went into r2
> row 5 went into r3
> row 6 went into r3
>
> r11 is empty !!!
> Would you clarify how data is distributed, when all partitions will be
> hit and when some of them
>
> Thanks again Powell

I'm a bit rushed right now so I am not going to explain why you are getting the result you are but I will say that your partitioning scheme makes no sense as presented.

If you have reason to range partition by more than just the "Date", by the way DATE is a reserved word so stop using it) please explain how a range that includes two or three columns relates to partition pruning your queries. If you can't then throw away what you are doing and partition by your date column only.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 06 2008 - 12:42:19 CST