RE: RE: Slow select distinct

From: Shaw John-P55297 <P55297_at_motorola.com>
Date: Tue, 30 Apr 2002 08:41:13 -0800
Message-ID: <F001.004538A7.20020430084113@fatcity.com>

Thanks for the idea - however the alternative you suggested actually ran slower.
It's seems to be the size of the distinct sort.

-----Original Message-----
Sent: Tuesday, April 30, 2002 9:59 AM
To: Multiple recipients of list ORACLE-L

>----- Original Message -----
>From: Shaw John-P55297 <P55297_at_motorola.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>Sent: Tue, 30 Apr 2002 06:13:42
>
>Unfortunately there are duplicates so they do need
>the distinct clause. I
>have tried several permutations of the query and
>distinct clauses including
>the most popular alternative:
>select distinct wrecks
>from
>(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);
>However it all seems to go back to the sort on the
>9500 rows being returned.
>

Try a 'divide and conquer' approach then, eliminating duplicates first as they might occur from several rows in STREET_REQS matching the same row in LOCATION, then by eliminating the remaining duplicates. Should lighten the burden of sorting. What about :

select DISTINCT LTRIM(L.STREET_ADDRESS_BEGIN||'

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

WHERE L.ID = SR.ID
  AND L.STREET_NAME IS NOT NULL; Regards,

Stephane Faroult
Oriole

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroul
  INET: sfaroult_at_oriolecorp.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 - 11:41:13 CDT