Re: Tunning question - DISTINCT or GROUP BY?

Interesting question. The obvious advice would be to do explain plans on both ways and benchmark the results using tkprof (quite easy if you haven't done it before). A difference in explain plan there certainly is.

It doesn't end there though, as it only takes one carefully crafted query by someone who knows the internal workings of sql intimately to demonstrate how the explain plan can change. I always believed that a group by could be used as a substitute for an order by (in many cases). I somtimes used it to do an "order by" in an inline view prior to 8i. See the URL ==>
http://www.orafans.com/ubb/Forum6/HTML/002393.html

CREATE TABLE zzz

(	col1	NUMBER,
	CONSTRAINT PK_zzz PRIMARY KEY (col1 ));
	
1.) select col1 from zzz where col1 > 123;
SELECT STATEMENT		  	 	 	 	      	             	 
  INDEX RANGE SCAN	PK_ZZZ	  	 	 	 	      	             	 

2.) select col1 from zzz where col1 > 123 group by col1;
SELECT STATEMENT		  	 	 	 	      	             	 
  SORT GROUP BY		  	 	 	 	      	             	 
    INDEX RANGE SCAN	PK_ZZZ	  	 	 	 	      	             	 

3.) select distinct col1 from zzz where col1 > 123;
SELECT STATEMENT		  	 	 	 	      	             	 
  SORT UNIQUE		  	 	 	 	      	             	 
    INDEX RANGE SCAN	PK_ZZZ