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 <[EMAIL PROTECTED]>
>To: Multiple recipients of list ORACLE-L
><[EMAIL PROTECTED]>
>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: [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: Shaw John-P55297
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).
