Query takes too long to run

Message from: drapkin11

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
;

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.


Historical Messages

Author: drapkin11
Date: Mon Jan 17 11:05:27 PST 2011
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
;

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.

__


Message from: drapkin11

This was posted twice for some reason.


Historical Messages

Author: drapkin11
Date: Mon Jan 17 11:16:04 PST 2011
This was posted twice for some reason.
__

Author: drapkin11
Date: Mon Jan 17 11:05:27 PST 2011
Message from: drapkin11 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 __


This was posted twice for some reason.

Evening,

you are not giving us much to go on here I'm afraid, so more questions
that answers:

  • What version of Oracle?

  • CBO or RBO?

  • If CBO, you have gathered stats recently?

Try this:

Explain plan for
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
;

Followed by select * from table (dbms_xplan.display);

Then do it again, but uncomment the line you mention.

Compare the two plans. I rather suspect you need an index on q1 and/or
q3 postal_code and/or to make sure that both tables q1 and q3 define
postal_code with the same data type and size. Ie, both number or both
varchar2(xxx) or whatever.

Your use of NVL(upper()) on iso_country_code and language_code will
prevent indexes being used on those two columns also. I would assume
(always bad!) that these should always be in upper case anyway so a
trigger on update/insert should always force those tow columns into
upper case (or you could set up a function based index on both columns
instead).

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.

Yes, you know that, but Oracle doesn't. Check the two explain plans
produced above and see if you can see a difference. That will tell you,
or at least give you a start.

I'm thinking that you probably have a hash join or nested loop, probable
the latter, to carry out the checks on postal_code equality.

Additionally, does this work:

select ...
from street3 q3
inner join street1 q1 on (
q1.PERM_ID=q3.PERM_ID
and nvl(upper(q1.ISO_COUNTRY_CODE) 'x') =
nvl(upper(q3.ISO_COUNTRY_CODE),'x')
and nvl(uppe(q1.LANGUAGE_CODE),'x') =
nvl(upper(q3.LANGUAGE_CODE),'x')
and q1.POSTAL_CODE=q3.POSTAL_CODE
) where
rownum

In other words, put all the join conditions into the join. You appear to
be using a mix of ANSI and old style joins here. It may be throwing the
optimiser out. (Of course, I could be wrong!)

--
Cheers,
Norm. [TeamT]

Message from: drapkin11

Thanks for the feedback. I’ve moved on to other work for now - and moved
this to the ‘resolve later’ pile!


Historical Messages

Author: drapkin11
Date: Mon Feb 14 15:22:57 PST 2011
Thanks for the feedback. I’ve moved on to other work for now - and moved
this to the ‘resolve later’ pile!

__

Author: Norman Dunbar
Date: Mon Jan 17 12:18:43 PST 2011
Evening, you are not giving us much to go on here I’m afraid, so more questions
that answers: * What version of Oracle? * CBO or RBO? * If CBO, you have
gathered stats recently? Try this: Explain plan for 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 > 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. Yes, you know that, but Oracle doesn’t. Check the two
explain plans produced above and see if you can see a difference. That will tell
you, or at least give you a start. I’m thinking that you probably have a hash
join or nested loop, probable the latter, to carry out the checks on postal_code
equality. Additionally, does this work: select … from street3 q3 inner join
street1 q1 on ( q1.PERM_ID=q3.PERM_ID and nvl(upper(q1.ISO_COUNTRY_CODE) ‘x’) =
nvl(upper(q3.ISO_COUNTRY_CODE),‘x’) and nvl(uppe(q1.LANGUAGE_CODE),‘x’) =
nvl(upper(q3.LANGUAGE_CODE),‘x’) and q1.POSTAL_CODE=q3.POSTAL_CODE ) where
rownum __

Author: drapkin11
Date: Mon Jan 17 11:16:04 PST 2011
This was posted twice for some reason.
__

Author: drapkin11
Date: Mon Jan 17 11:16:04 PST 2011
Message from: drapkin11 This was posted twice for some reason.
_______________________________________ Historical Messages Author: drapkin11
Date: Mon Jan 17 11:16:04 PST 2011 This was posted twice for some reason. __
Author: drapkin11 Date: Mon Jan 17 11:05:27 PST 2011 Message from: drapkin11 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 __


__

Author: drapkin11
Date: Mon Jan 17 11:05:27 PST 2011
Message from: drapkin11 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 __


Thanks for the feedback. I’ve moved on to other work for now - and moved this to the ‘resolve later’ pile!