RE: Slow select distinct

John,

        If you don't use the DISTINCT, how many records are returned?

        The problem is that DISTINCT forces a sort, no way around it. Are you sorting in memory or disk?

Dan Fink

-----Original Message-----
John-P55297
Sent: Monday, April 29, 2002 3: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: Daniel W. Fink
  INET: optimaldba_at_yahoo.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).