Re: SQL DISTINCT alternate

"Toepke, Kevin M" wrote:
>
> As I see it, there are several questions here:
> 1) Can I use DISTINCT to do a sort?
> Yes and no. In Oracle 7, a DISTINCT does an implicit sort
> In Oracle 8, it does a "SORT NOSORT" operation. Dupicates are
> removed but the output is NOT guaranteed to be in sorted order.
> 2) How do I sort the output?
> Use the "ORDER BY" clause as in:
> SELECT a, b, c FROM tab_1 ORDER BY a, b, c
> 3) How do I get around the performance hit of a SORT operation?
> In Oracle 8.0 through 8.1.6 (fixed in 9i and 8.1.7) there is a bug
> that causes the optimizer to choose a horrendous execution path if you are
> ordering by a column that is referenced in the WHERE clause.
>
> Workaround From:
> SELECT a, b, c FROM tab_1 WHERE a > 33 ORDER BY a, b, c:
> to:
> SELECT /*+ NO_MERGE(d) */ a, b, c FROM (
> SELECT a, b, c FROM tab_1 WHERE a > 33
> ) ORDRE BY a, b, c;
>
> Kevin
>
> -----Original Message-----
> Sent: Wednesday, June 20, 2001 1:50 PM
> To: Multiple recipients of list ORACLE-L
>
> Hi
> In a sql stmt what will be the impact if we used DISTINCT clause and how we
> can overcomes on the performance bottleneck caused by DISTINCT.
> like ... SELECT DISTINCT A,B,C FROM TAB_1
> I want to get same output without using distinct?Is this possible?If, YES
> then how?
> Thanks in advance
> -Seema

I can't see any way of getting rid of a DISTINCT if a single table is involved - unless you are querying the wrong table and in fact you'd rather query a table for which what you are looking for is the primary key. When I see a DISTINCT and a join, it usually smells of lazy programming and in most cases you can get rid of it with a correlated subquery.

-- 
Regards,

Stephane Faroult
Oriole Corporation
Voice:  +44  (0) 7050-696-269 
Fax:    +44  (0) 7050-696-449 
Performance Tools & Free Scripts
--------------------------------------------------------------
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--------------------------------------------------------------
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).