Re: Slow select distinct

Shaw John-P55297 wrote:
>
> 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
Checking the execution plan in both case could have been interesting. In any case, DISTINCT is rarely necessary with a join. Look at your query. Everything comes from the LOCATION table, STREET_REQS just happens to be here to provide a filter. Unless LOCATION holds duplicates for the columns you want to bring back, you can avoid the DISTINCT. Both 'IS NULL' and 'IS NOT NULL' (less sure about the later with latest versions) usually translate as 'full scan', so forget them if you wish to get quickly to the data. Hopefully (and your DISTINCT-less result seems to indicate this) the TYPE and/or CODE conditions are very selective. If scanning LOCATION is not too painful, this can give good results :

 select LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||'  '||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS  FROM LOCATION L
 WHERE L.STREET_NAME IS NOT NULL
   AND EXISTS (SELECT NULL

               FROM STREET_REQS SR
               WHERE SR.TYPE IN ('KED','KAD')
                 AND SR.CODE LIKE 'O%'
                 AND SR.ORIG_STREET_REQ_ID IS NULL
                 AND SR.ID = L.ID)

Another solution could be:

select LTRIM(L.STREET_ADDRESS_BEGIN||' '||L.STREET_NAME_PREFIX||'  '||L.STREET_NAME||' '||L.STREET_NAME_SUFFIX) WRECKS  FROM LOCATION L
 WHERE L.STREET_NAME IS NOT NULL
   AND L.ID IN (SELECT SR.ID

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

However, this is unlikely to be efficient if the inner query returns 10,000 rows.
Do not play with parameters before having tried everything else.  

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).