Re: Why DISTINCT is costly?

A group by is not always the better choice but in most cases you can see it's better when you look at the explain plan of your query. Here's an example:

SELECT	 A.DF_VERTRAGS_NR,
	 	 A.DF_BEGINN_DATUM_VTR,
	 	 A.DF_AD_HOC_KZ_1,
	 	 A.DF_AD_HOC_KZ_2,
	 	 A.DF_AD_HOC_KZ_3,
	 	 A.DF_SACHPREIS_NR,
	 	 A.DF_EINLOESUNG_DTM,
	 	 A.DF_SONDER_BSV_KZ
FROM	 EXT_TDVC_VERTRAG A
GROUP BY A.DF_VERTRAGS_NR,
	  	 A.DF_BEGINN_DATUM_VTR,
		 A.DF_AD_HOC_KZ_1,
	 	 A.DF_AD_HOC_KZ_2,
	 	 A.DF_AD_HOC_KZ_3,
	 	 A.DF_SACHPREIS_NR,
	 	 A.DF_EINLOESUNG_DTM,
	 	 A.DF_SONDER_BSV_KZ

This statement is significally faster than

SELECT	 DISTINCT A.DF_VERTRAGS_NR,
	 	 A.DF_BEGINN_DATUM_VTR,
	 	 A.DF_AD_HOC_KZ_1,
	 	 A.DF_AD_HOC_KZ_2,
	 	 A.DF_AD_HOC_KZ_3,
	 	 A.DF_SACHPREIS_NR,
	 	 A.DF_EINLOESUNG_DTM,
	 	 A.DF_SONDER_BSV_KZ
FROM	 EXT_TDVC_VERTRAG A

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		8 K	 	26,6206304779564

  SORT GROUP BY		8 K	733 K	26,6206304779564
    EXTERNAL TABLE ACCESS FULL	DWH_REA.EXT_TDVC_VERTRAG	8 K	733
K	24,4384919334481


Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS		8 K	 	201,302378376216

  SORT UNIQUE		8 K	733 K	201,302378376216
    EXTERNAL TABLE ACCESS FULL	DWH_REA.EXT_TDVC_VERTRAG	8 K	733
K	24,4384919334481

sim Received on Mon Sep 12 2005 - 07:05:23 CDT