Re: Why DISTINCT is costly?

Noons wrote:
> Robert Klemme wrote:
>
>> There's not much difference in those plans. Do you have an
>> explanation why SORT GROUP BY is cheaper than SORT UNIQUE in this
>> case? Thx!
>
> There is a good difference.

Which one apart from the cost and the SORT GROUP BY vs. SORT UNIQUE. Did I overlook something?

> The other thing of course is that
> DISTINCT applies to all columns in the select list whereas group
> by only applies to columns in the group by list. If the group by
> list is smaller than the select list, you have a need to sort a
> lot less bytes. Which translates into a faster overall operation.

As far as I can see this is not the case here (and also with your example SELECT A.DF_VERTRAGS_NR,...). So I'm still wondering about the difference.

> 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.

> Too often it's just taken by developers/designers as an easy fix
> to an incorrectly specified query in the first place. Usually
> for fear of including more tables in a join in the mistaken assumption
> that it it is bad for performance. In most such cases, DISTINCT will
> turn out to be a worse solution.

Yeah, probably.

Kind regards

    robert Received on Wed Sep 14 2005 - 08:30:24 CDT