There are also a lot of cases where you can get rid of a subquery. Consider
the following
SELECT DISTINCT emplid
FROM emp_history eh1
WHERE salary > 100000;
SELECT emplid
FROM emp_history eh1
WHERE rowid = (SELECT MAX(rowid)
FROM emp_history eh2
WHERE eh1.emplid = eh2.emplid
AND eh2.salary > 100000)
Kevin
-----Original Message-----
Sent: Wednesday, June 20, 2001 4:17 PM
To: Multiple recipients of list ORACLE-L
"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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Toepke, Kevin M
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
