joe bayer 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.
>
> 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?
Partition by week and the problem disappears completely doesn't it?
Right now your query hits 4.3 weeks worth of data possibly crossing
to 8.6 weeks. Partition by week and no query will look at more than
2 weeks worth of data.
--
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed May 25 2005 - 18:30:24 CDT
