Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Help: How should i partition this table

Re: Help: How should i partition this table

From: Paul Dixon <paul.gp.dixon_at_bt.com>
Date: 26 May 2005 02:19:23 -0700
Message-ID: <1117099163.253191.311400@o13g2000cwo.googlegroups.com>

joe bayer (no-spam) wrote:
> I am using oracle 92. I have a table which has two date column,
> begining_time, and ending_time.
>
> The query usually is
> where
> begining_time > :b1
> and ending_time < :b2
>
> If i partition by beging_time, performace will degrade if :b1 gets smaller,
> if I partition by ending_time, the performace will degrade if :b2 gets
> bigger. I tried
>
> partition by range (ending_time, starting_time), the performace seems to be
> worse.
>
> Your help is highly appreciated.

Joe,

It may be worth looking at your "usual query" first, to see if it can be
rewritten into something which eliminates as many partitions in your proposed partitioned table as possible. I'm assuming that ending_time cannot
be before begining_time (a very big assumption on my part) and that the "usual query" could therefore be rewritten as :-

where
begining_time between :b1 and :b2
and ending_time < :b2

Which would be good for a table partitioned by range against beginning_time.

or

where
begining_time > :b1
and ending_time between :b1 and :b2

Which would be good for a table partitioned by range against ending_time.

You should also consider when the records are going to be initially inserted
into the table. If the record is inserted at beginning_time and initially
without a value for ending_time then partitioning by ending_time is probably
not going to be appropriate. If the record is first inserted at or after
ending_time, then partitioning by ending_time may be more suitable.

Finally, as Daniel Morgan has already mentioned, you need to identify a suitable partition range interval for the table. One factor to be considered
will be the range of intervals that you expect to see in your "usual query".

Paul Dixon Received on Thu May 26 2005 - 04:19:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US