Re: Help: How should i partition this table

"joe bayer" <joebayerii(no-spam)@hotmail.com> 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

That is a multi-dimensional query which is best supported (in the general case) by a spatial index. But to do that you can't simply create the index, you also have to rewrite the query into something that looks like Hell. Also, I gather that spatial indices have a lot of overhead and are hard on concurrency. And probably don't work on partitioned tables, but thats just going from memory and on an older version.

> 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.

Are you partitioning solely in an attempt to improve this query, or is the partitioning supposed to server other purposes as well?

When you partition on begining_time, do you use a local index on ending_time (and vice versa)?

What is more common, that :b1 is small or :b2 is big?

> I tried
>
> partition by range (ending_time, starting_time), the performace seems to
> be worse.

I'm not surprised, as there is no reason to think that that would make performance better.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB