| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Multi-Column Partitions
I seemed to have narrowed down the problem. It appears to be the combination of the numeric and date columns together.
I have tried
1. PARTITION BY RANGE (usage_type_code,endtime,starttime)
and
2. PARTITION BY RANGE (endtime,starttime,usage_type_code)
A selection of values would be:
USAGE_TYPE_CODE STARTTIME ENDTIME
--------------- ------------------ -------------------- 10 02-JAN-2000 02:25:00 02-JAN-2000 04:03:13 10 03-JAN-2000 15:43:00 02-JAN-2000 17:50:00 30 04-JAN-2000 06:15:00 02-JAN-2000 06:17:00
The usage_type_code of 30 is acounted for in the partitioning scheme, I just didn't show all of the partitions in the first discription of the problem. When I try either of the partitioning scheme above Oracle appears to select the first column type (numeric or date) and ignores the rest. For example, if I use the first partition scheme then all of the rows go to the first partition defined by the USAGE_TYPE_CODE and the dates are ignored. If I choose the second partition scheme then the rows go to the dates properly but the USAGE_TYPE_CODE is ignored. This has been confirmed with the the following:
SELECT *
FROM um_usage PARTITION (p_um_usage_20000102_10)
I am wondering if Oracle has a problem with combining date and numeric partition keys. I have tried the partitioning with only dates and get good results. I have tested various partition schemes with dates only using typical date range searches and have found that both date columns are needed in order to get the appropriate partition pruning. Our typical query would look like this:
SELECT *
FROM um_usage
WHERE endtime BETWEEN TO_DATE('03-JAN-2000 00:00:00, 'DD-MON-YYYY HH24:MI:SS')
AND TO_DATE('04-JAN-2000 24:59:59, 'DD-MON-YYYY HH24:MI:SS')
OR
This query gathers all of the records we need and with a partition scheme using (ENDTIME, STARTTIME) prunes the unneeded partitions effectively.
For us the issue is that we also divide the rows into types which further identifies the records. Currently we have about 25 types and will have another 25 to 30 types in a few months. The vast majority of the records fall within just 5 of these types. On a daily basis we are loading about 4.5 million rows a day and expect that to grow to 12 million or so rows by next year. so you can see that partitioning is really needed at this time. The current system is a legacy of Oracle 7.x and we have been using UNION type views on numerous tables to achieve what can be done (hopefully) in a single partitioned table.
Anyway, thanks for your interest. Any help will of course be greatly appreciated, blah, blah, blah.... :-)
Thanks
Mike Winterer
mailto:mikew3_at_corp.earthlink.net
"Sleep is a wholly inadequate substitute for coffee."
Jonathan Lewis wrote:
> 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 multi-
> column 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 Mon Mar 20 2000 - 00:00:00 CST
 |
 |