Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 11 Feb 2005 @ 17:13:10 GMT

  <Prev Next>   <<First <Prev Next> Last>>  

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 records?  

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 :-)


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023