This may be my favorite Oracle 8i bugs (and no, I don't have the bug#s.)
Performing a sort sometimes causes wildly inefficient execution plans.
I'm guessing that if you run the explain plans for the query without the
DISTINCT and with the DISTINCT you will get completely different results. If
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||'
- 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
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
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
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
