Re: BUG #5652: Optimizer does wrong thing with partitioned tables

Jeff Davis wrote:
> On Fri, 2010-09-10 at 08:10 -0700, Chris Travers wrote:
>
>> Just adding my voice to the "fix it" camp.  Is there any reason the
>> table scans in this sort of thing cannot be independently planned?
>>
>
> I don't think it's about independent planning. For instance, AVG clearly
> can't be planned this way, there are particular properties of MAX that
> allow the optimization:
>
>   1. MAX(x) can be rewritten as: ORDER BY x DESC LIMIT 1
>   2. The MAX of set S is the MAX of the MAXes of each partition of S
>
> The optimizer knows about the former, but not the latter.
>
> Regards,
>     Jeff Davis
>
>
Jeff, that's the problem. Functions like "MAX" are rather ordinary and
frequently used. Using sequential scan to read all partitions is the
wrong thing to do. I agree that AVG() cannot be computed using index but
MAX() and MIN() can. I will send you personally 2 versions of a script
that I am still writing, just to see to what extent do I go to get the
necessary performance. Optimizer definitely needs fixes when it comes to
partitions.

--

Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions