Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!news-south.connect.com.au!news1.optus.net.au!optus!news.uwa.edu.au!nntp.waia.asn.au!203.59.27.186.MISMATCH!newsfeed.iinet.net.au!newsfeed.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsstorage1.iinet.net.au!per-qv1-newsreader-01.iinet.net.au!not-for-mail Date: Wed, 14 Sep 2005 23:52:04 +1000 From: Noons User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server,comp.databases.oracle.misc Subject: Re: Why DISTINCT is costly? References: <1126507505.386016.211310@f14g2000cwb.googlegroups.com> <1126513158.832436.51790@g43g2000cwa.googlegroups.com> <3ol0s0F6e343U1@individual.net> <1126526723.283046.268330@g49g2000cwa.googlegroups.com> <3olbmlF61id4U1@individual.net> <1126581392.774559.29130@o13g2000cwo.googlegroups.com> <3oqmviF74cqeU1@individual.net> In-Reply-To: <3oqmviF74cqeU1@individual.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Lines: 31 Message-ID: <43282b05$0$12651$5a62ac22@per-qv1-newsreader-01.iinet.net.au> NNTP-Posting-Host: 203.217.25.119 X-Trace: 1126705925 per-qv1-newsreader-01.iinet.net.au 12651 203.217.25.119 Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:251479 comp.databases.oracle.misc:121859 Robert Klemme apparently said,on my timestamp of 14/09/2005 11:30 PM: > > Which one apart from the cost and the SORT GROUP BY vs. SORT UNIQUE. Did > I overlook something? IIRC, there was a significant difference in bytes processed? The text was a bit mangled due to wrapping, maybe I misread it. > >>But the best solution is always to avoid the DISTINCT. > > > It's certainly good to have these rules of thumb. However me thinks it's > even better to understand why these rules apply. That's why I ask. Why they apply is simple: DISTINCT forces a unique sort, no matter what. You may be able to slightly speed the sort with indexes, but it ain't never gonna be as fast as joining to the necessary tables to avoid the duplicates in the first place. If a join solves the problem one is trying to solve, then it is a preferable solution. There are a few examples around of this kind of design problem, I'll see if I can find some URLs for you. -- Cheers Nuno Souto in sunny Sydney, Australia wizofoz2k@yahoo.com.au.nospam