Archives of the TeradataForum
Message Posted: Fri, 11 Feb 2005 @ 17:13:10 GMT
| Subj: || || Re: Guidelines for using Distinct vs. group by |
| From: || || Gaitonde, Amol A |
Here are some of guidelines from previous thread.
From: Dieter Noeth
Sent: Sunday, December 05, 2004 14:19
Subject: Re: Why is Group-by Faster than Distinct
| ||Is it a standard optimization technique now to use Group by instead of Distinct to optimize queries that need to remove duplicate
No, it depends...
All PI columns -> Group By
Lots of rows per AMP per value -> Group by
Up to a few rows per AMP per value -> Distinct
And remember, that a subquery is distinct by default, but you can override it with group by .
| ||Is there any difference in results, in case I am going to use group by instead of distinct?|| |
Distinct returns an ordered result set, group by doesn't.
| ||If there is no difference why doesn't the optimizer use the duplicate removal operation which is faster in Group by instead of the
one that is use in distinct?|| |
AFAIK there's already an enhancement request for that :-)