Re: Help: How should i partition this table

Thanks very much for your help. We have about 10 yrs of data, and we are partition the table by month only for the performance.

Say, if we partition by ending_time, the following query

"select count(*) from A where biging_time > sysdate and ending_time < to_date('1995/01/01','yyyy/mm/dd')";

 will be very fast because there is only one partition that needs to be scanned. But query

"select count(*) from A where biging_time > sysdate and ending_time < to_date('2005/01/01','yyyy/mm/dd');"

will be very slow, because all the partitions will be scanned.

The same thing holds true if we partition against starting_time.

We have local index on both begining_time and ending_time, but index is not used because (i guess) all the columns has to be selected, so Oracle thinks full table scan will be cheaper.

Any idea how to partition the table?

<xhoster_at_gmail.com> wrote in message
news:20050524170234.903$Gf_at_newsreader.com...
> "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
Received on Wed May 25 2005 - 18:18:38 CDT