Re: Why DISTINCT is costly?

Noons wrote:
> Robert Klemme apparently said,on my timestamp of 14/09/2005 11:30 PM:
>
>>
>> Which one apart from the cost and the SORT GROUP BY vs. SORT UNIQUE.
>> Did I overlook something?
>
> IIRC, there was a significant difference in bytes processed?
> The text was a bit mangled due to wrapping, maybe I misread it.
>
>>
>>> But the best solution is always to avoid the DISTINCT.
>>
>>
>> It's certainly good to have these rules of thumb. However me thinks
>> it's even better to understand why these rules apply. That's why I
>> ask.
>
> Why they apply is simple: DISTINCT forces a unique sort, no matter
> what. You may be able to slightly speed the sort with indexes, but it
> ain't never gonna be as fast as joining to the necessary tables to
> avoid
> the duplicates in the first place.
>
> If a join solves the problem one is trying to solve, then it is
> a preferable solution. There are a few examples around of this kind
> of design problem, I'll see if I can find some URLs for you.

It seems we are talking cross each other... Rewind. Ok, I'll try again: there is this simple example which selects some columns from a table - one SQL statement uses DISTINCT the other uses GROUP BY. The output is equivalent as far as I can see. Query plans are quite similar (they are simple) with the only differences being SORT GROUP BY vs. SORT UNIQUE and the figures (cost or bytes). A join won't help here as far as I can see. So we can leave that out for the moment. Now I'm still wondering where exactly the difference is in processing these two selects. It seems there is a cost difference attached to SORT GROUP BY vs. SORT UNIQUE but why? Is it just that the latter benefits filled caches? Or is there some kind of algorithmic difference between SORT GROUP BY and SORT UNIQUE?

I hope I was clearer now.

Kind regards

    robert Received on Wed Sep 14 2005 - 09:27:58 CDT