Re: Multi-Column Partitions

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 2000/03/18
Message-ID: <953365771.8814.0.nnrp-03.9e984b29@news.demon.co.uk>#1/1

It would be easier to give a precise explanation if you gave us a 3 or 4 sets values that all go into the first partition when you don't expect them too.

However, what you are seeing is probably caused by the commonest misinterpretation of how multicolumn  partitions work. The best analogy when using them is to think of a multi-column index.

Take you partition bounded by

    11, 2nd Jan, 2nd Jan

The value (10, 1st Jan, 1st Jan) goes into it because 10 < 11

The value (11, 1st Jan, 1st Jan) goes into it because

    11 = 11
    1st Jan < 2nd Jan

The value (11, 2nd Jan, 1st Jan) goes into it because

        11 = 11
        2nd Jan = 2nd Jan
        1st Jan < 2nd Jan

Similarly (11, 1st Jan, 4th Jan) goes into it because

        11 = 11
        1st Jan < 2nd Jan

and the 4th Jan item doesn't even need to be tested in your particular scenario.

The columns of a partitioning key are not independent a column is tested only if Oracle cannot determine from the previous columns which partition it goes into.

I hope this helps. If not, post a few of the actual values that you think are going wrong, and I'll see if there is any other explanation.

--

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

Mike Winterer wrote in message <38D2C0DD.87430372_at_corp.earthlink.net>...


>I am having trouble getting data to be distributed to the appropriate

>partitions.  Using the definition below, all of the data is inserted

>into the the first partition.  This happens even if the dates are

>actually spread over the various dates.  It may be a cause of multiple

>date columns in the partition definition.  Can anyone help on this?

>

>CREATE TABLE um_usage

>(

>  usage_type_code        NUMBER,

>  userid                 VARCHAR2(50) NOT NULL,

>  starttime              DATE         NOT NULL,

>  endtime                DATE         NOT NULL

>)

>PARTITION BY RANGE (usage_type_code,endtime,starttime)

>  (

>    PARTITION p_um_usage_20000101_10  VALUES LESS THAN ( 11,

>TO_DATE('01-JAN-2000','DD-MON-YYYY'),TO_DATE('01-JAN-2000','DD-MON-YYYY')),

>

>    PARTITION p_um_usage_20000102_10  VALUES LESS THAN ( 11,

>TO_DATE('02-JAN-2000','DD-MON-YYYY'),TO_DATE('02-JAN-2000','DD-MON-YYYY')),

>

>    PARTITION p_um_usage_20000103_10  VALUES LESS THAN ( 11,

>TO_DATE('03-JAN-2000','DD-MON-YYYY'),TO_DATE('03-JAN-2000','DD-MON-YYYY')),

>

>   ...

>  )

>

>

Received on Sat Mar 18 2000 - 00:00:00 CST