Toad World® Forums

Query takes too long to run


#1

I ran the following query:

select
q1.ISO_COUNTRY_CODE q1country
,q3.ISO_COUNTRY_CODE q3country
,q1.LANGUAGE_CODE q1lang
,q3.LANGUAGE_CODE q3lang
,q1.POSTAL_CODE pcode1
,q3.POSTAL_CODE pcode3
,case when q1.POSTAL_CODE=q3.POSTAL_CODE then 1 else 0 end as “check”
from street3 q3
inner join street1 q1 on q1.PERM_ID=q3.PERM_ID
where
nvl(upper(q1.ISO_COUNTRY_CODE),‘tempnull’)=nvl(upper(q3.ISO_COUNTRY_CODE),‘tempnull’)
and nvl(upper(q1.LANGUAGE_CODE),‘tempnull’)=nvl(upper(q3.LANGUAGE_CODE),‘tempnull’)
–and q1.POSTAL_CODE=q3.POSTAL_CODE
and rownum<10
;

Results were returned in less than a second:

Q1COUNTRY,Q3COUNTRY,Q1LANG,Q3LANG,PCODE1,PCODE3,check
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
USA,USA,ENG,ENG,49946,49946,1
… etc.

When I uncomment the line ‘and q1.POSTAL_CODE=q3.POSTAL_CODE’ in the WHERE clause, however, the query takes forever to return. This doesn’t make sense to me since the postcodes are equal in most cases anyway.