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: [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: Daniel W. Fink
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).
