Re: Help: How should i partition this table

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

If performance is the only reason for paritioning, then you should take a step back. Maybe the best way to improve performance doesn't even involve partitioning.

>
> 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')";

What are the restrictions on the relation betwen biging_time and ending_time? Just based on the column names, one could guess that biging_time < ending_time, as things generally begin before they end. (If so, see another post in this thread.) But from the above query that doesn't seem to be the case. What does the distribution of (biging_time - ending_time) look like? What does the distribution of actual query endpoints look like?

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

What exactly is the index? I think you would need a nonprefixed local index to be most likely to benefit. (But note that I've never actually seen Oracle use the access method I'm thinking of here. But then again, I've never played around with partitioning.)

Xho

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