Oracle-L: RE: Slow select distinct

From: Toepke, Kevin M <ktoepke_at_trilegiant.com>
Date: Tue, 30 Apr 2002 04:18:27 -0800
Message-ID: <F001.0045304C.20020430041827@fatcity.com>

This may be my favorite Oracle 8i bugs (and no, I don't have the bug#s.) Performing a sort sometimes causes wildly inefficient execution plans.

I'm guessing that if you run the explain plans for the query without the DISTINCT and with the DISTINCT you will get completely different results. If this is my favorite bugs, then you will get SORT/MERGE joins when you add the DISTINCT where the joins were either NESTED LOOPS or HASH joins before.

My typical solution is to use an inline view and the NO_MERGE hint as in: SELECT /*+ NO_MERGE(data) */

DISTINCT wrecks
FROM (SELECT LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||

               ''||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS
        FROM   LOCATION L,
               STREET_REQS SR
        WHERE L.ID = SR.ID
        AND L.STREET_NAME IS NOT NULL
        AND SR.TYPE IN ('KED','KAD')
        AND SR.CODE LIKE 'O%'
        AND SR.ORIG_STREET_REQ_ID IS NULL) data

Caver

-----Original Message-----
Sent: Monday, April 29, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L

I got a query that selects a list of addresses based an occurence at that location.
this query comes back in less than 2 seconds without a distinct clause on the concatenated name. When I add the distict clause it takes over 40 seconds. I've tried adjusting various sort area sizes and buffer sizes to see if I can speed this up. Anybody have a clue if there is something in particular that I can check.
8.1.6 on NT rules based. wtihout the distinct clause it brings back about 10,000 records.
select DISTINCT LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||' '||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS FROM LOCATION L,
STREET_REQS SR
WHERE L.ID = SR.ID
AND L.STREET_NAME IS NOT NULL

  AND SR.TYPE IN ('KED','KAD')
  AND SR.CODE LIKE 'O%'
  AND SR.ORIG_STREET_REQ_ID IS NULL

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Shaw John-P55297
INET: P55297_at_motorola.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Toepke, Kevin M
INET: ktoepke_at_trilegiant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------