Re: Multi-Column Partition keys

From: Mike Winterer <mikew3_at_corp.earthlink.net>
Date: Wed, 09 Feb 2000 18:39:56 GMT
Message-ID: <38A1B481.39B4B16F@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)     ( 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'))
        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 ..
> > 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 )
> >
> > 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 - 12:39:56 CST