RE: Slow select distinct

From: Shaw John-P55297 <P55297_at_motorola.com>
Date: Tue, 30 Apr 2002 09:03:33 -0800
Message-ID: <F001.00453A17.20020430090333@fatcity.com>

If not distinct there's about 9900 records, there are some physical rw' going on
but I've bumped sort area up to 2M.

-----Original Message-----

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

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).
--

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). Received on Tue Apr 30 2002 - 12:03:33 CDT