Re: Why DISTINCT is costly?

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

But the best solution is always to avoid the DISTINCT.

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. Received on Mon Sep 12 2005 - 22:16:32 CDT