Re: GROUP BY

"Marshall" <marshall.spight_at_gmail.com> wrote in message news:1179541160.527580.51400_at_q23g2000hsg.googlegroups.com...
> On May 18, 12:31 pm, "David Cressey" <cresse..._at_verizon.net> wrote:
> >
> > A lot of newbie SQL writers learn to avoid "select distinct"
> > because "it runs too slow".
>
> Once I had a query that I happened to notice ran *faster*
> when I added distinct, even though the results were the
> same either way. I wish I'd written it down.
>
>
I've seen this a lot of times. I think I understand what's going on.

A cost based optimizer estimates a cost of using a sorted index for a range retrieval. Sometimes that estimate is a little off, compared with the cost estimate of alternative strategies.

When "select distinct" is used, the optimizer has to take into account the cost of sorting the result. A strategy that uses a range retrieval on a sorted index may be able to skip the sort. Thus the optimizer uses the sorted index with "select distinct" but not with "select".

If the optimizer was wrong about the cost of using the index, you will see the behavior you report. Received on Sat May 19 2007 - 16:01:51 CEST