Re: Multi-Column Partition keys

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 9 Feb 2000 21:44:39 -0000
Message-ID: <950132836.25150.0.nnrp-06.9e984b29@news.demon.co.uk>

Sorry, I had assumed you meant 2-dimensional partitioning because 2-column single-dimension partitioning won't help.

BTW - you ought to use a 4-digit year when defining the partitions - oracle will (I think) let you get away with 2-digits and turn it into the full 4-digits in the internal 'high-value' column but better safe than sorry.

The problem with the two-column partition idea is that the end-date test will have to check every single partition - in much the same way that an index on (start_date, end_date) would be useless for a range
test on end_date.

Expanding on your original requirement
>> > where (starttime between '10-JAN-2000' and '11-JAN-2000')
>> > or (endtime between '10-JAN-2000' and '11-JAN-2000')
>> > or ('10-JAN-2000' between starttime and endtime )

and your example

>    ( PARTITION table_d1
>        VALUES LESS THAN
>           ( TO_DATE(`94-04-01','YY-MM-DD'),
>             TO_DATE(`94-04-01','YY-MM-DD'))
>        TABLESPACE tsa,
>      PARTITION table_d2
>        VALUES LESS THAN
>            (TO_DATE(`94-04-02','YY-MM-DD'),
>              TO_DATE(`94-04-02','YY-MM-DD'))

Assuming you are bounding each partition with starttime matching endtime

Test 1 will be restricted to the 2 partitions bounded by starttimes 11th and 12th Jan

Test 2 wouldn't be subject to any partition elimination at all.

Test 3 would be limited to all partitions below the one bounded by startime 11th Jan

Test 2 is the least intiutive case:
but the partition would be able to hold ANY datetime for endtime. This is easier to understand with numbers, but:

If you have 11th Jan 00:00, 11th Jan 00:00 as the partition limit, then 10th Jan 15:00 is less than 11th Jan 00:00, so

10th Jan 15:00 , 12th Jan 00:00
is inside the partition because the starttime is less than the partition limit for starttime

A numeric example -

With a partition boundary of 70, 90 the value 65,100 is inside the limit because 65 < 70.

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Mike Winterer wrote in message <38A1B481.39B4B16F_at_corp.earthlink.net>... >Actually I am talking about multi-column partitions not a multi-dimensional >partition. I was refering to a partition defined on a concatenated key. I'm

>still not sure if this is possible or desirable for the table I am
>considering.
>
>More specifically, can your define a multi-column partition using the
>following
>
>Example:
>
>  PARTITION BY RANGE (starttime, endtime)
>        TABLESPACE tsb,
>etc......
>
>And the question is whether this will do what I want, which is to eliminate
>partitions outside of a date range.  In the above example, some of the
>endtime dates will fall in partition table_d2 because the startime is

within
>the 1st date range, but the endtime falls after that. At least that is what

>I hope.  I also hope that Oracle will be able to recognise this.
>
>Any ideas or help? :-)
>
>
>Jonathan Lewis wrote:
>
>> First point - you can't do multi-dimensional
>> partitioning with partitioned tables (except
>> in limited cases with composite partitions).
>>
>> Second point - I don't think it wouldn't help
>> you if you could.  Your query allows start
>> time to range over all possibly values,
>> and also allows end time to range over all
>> possible values.
>>
>> Indexes on start time and end time separately
>> would help for the first two predicates, but
>> the main problem comes from the third
>> predicate.
>>
>> --
>>
>> Jonathan Lewis
>> Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

>>
>> Mike Winterer wrote in message <389F8338.329483B6_at_corp.earthlink.net>...
>> >We have a situation where there are 2 date columns (starttime and
>> >endtime) in a table and our most

>> > common query is of the form

>> >

>> > select ..
>> > from ..

>> >

>> > Note that the date range requested can be a day, week, or a month. Also

>> >note that the date cover

>> > the range of seconds between midnight and midnight. These are not

>> >TO_CHAR(:date) values.
>> >

>> > The question is "can we partition on both date columns?" If we

>> >partition on only one or the other

>> > dates then there will be some records outside of that single column

>> >range. With the query that we

>> > are using above we would eliminate some of the search but still have to

>> >scan the entire index to

>> > find the range of records of the non-partitioned column.

>> >

>> > If we can partition on both of the date columns how will that change

>> >our queries?
>> >
>> >
>

Received on Wed Feb 09 2000 - 15:44:39 CST