Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g3UH3wo13543 for ; Tue, 30 Apr 2002 13:03:58 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id JAA59224; Tue, 30 Apr 2002 09:49:00 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 00453A17; Tue, 30 Apr 2002 09:03:33 -0800 Message-ID: Date: Tue, 30 Apr 2002 09:03:33 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Shaw John-P55297 Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Shaw John-P55297 Subject: RE: Slow select distinct Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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@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@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@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@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@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@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).