Re: Spped of max

On Tue, May 14, 2002 at 01:35:47PM -0400, Edmund Dengler wrote:

>
> which flies as expected?  Now, this type of thing gets me real worried
> about how good the optimizer really is.

The max() (and min()) operations are not rewritten, as you note.
There's no rule for it.  There was a recent discussion about this --
check the archives.

There's a similar set of worries, apparently, for NOT EXISTS versus
NOT IN.  In the vast majority of cases, it seems, NOT EXISTS is
considerably faster than NOT IN; but not in all cases.  So the
queries are executed differently, and never get rewritten one to the
other.  The only answer is to try to make sure your criteria-written
queries are generated in a way that is usually pretty good.  For
sure, min() and max(), and NOT IN, and a couple of others (count()
comes to mind) are almost always losers, so you should avoid them.

A

--
----
Andrew Sullivan                               87 Mowat Avenue
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M6K 3E3
                                         +1 416 646 3304 x110