Re: performance of OR versus IN

From: W.Breitling <member28455_at_dbforums.com>
Date: Mon, 19 May 2003 23:24:20 +0000
Message-ID: <2899044.1053386660@dbforums.com>

Your two queries are totally different. The equivalent query to your in version using ORs would be

select count(p.personOID)
from Address a, AddressXPerson axp, Person p where (a.postalCode = '07010'

OR a.postalCode = '07020'
OR a.postalCode = '07022'
OR a.postalCode = '07024'
OR a.postalCode = '07030'
OR a.postalCode = '07032'
OR a.postalCode = '07047')

AND axp.addressOID = a.addressOID
AND axp.role = 'Practitioner'
AND p.personOID = axp.personOID;

Remember, AND binds stronger than OR so without the parentheses you do not have any join predicates for the postal codes except the last one and hance you hadve two cartesion joins. No wonder you have terrible performance.

Originally posted by Doug
> I get TERRIBLE performance from a query with several OR clauses and a
> few table joins; if I take away the table joins or change the OR
> clauses into an IN clause the problem goes away. Any explanations?
> (The IN clause was not originally available in the libraries of the
> application server that we are using, so this could mean a lot of
> changes.) Table info, queries and execution plans follow:
>
> Address has 113K records, AddressXPerson 188K and Person 287K.
>
> select count(p.personOID)
> from Address a, AddressXPerson axp, Person p
> where a.postalCode in
> ('07010','07020','07022','07024','07030','07032','07047')
> AND axp.addressOID = a.addressOID
> AND axp.role = 'Practitioner'
> AND p.personOID = axp.personOID;
>
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=CHOOSE (Cost=192 Card=1 Bytes=27)
> 1 0 SORT (AGGREGATE)
> 2 1 NESTED LOOPS (Cost=192 Card=59 Bytes=1593)
> 3 2 HASH JOIN (Cost=133 Card=59 Bytes=1357)
> 4 3 INLIST ITERATOR
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=28
> Card=57 Bytes=513)
> 6 5 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
> (NON-UNIQUE) (Cost=2 Card=57)
> 7 3 TABLE ACCESS (FULL) OF 'ADDRESSXPERSON' (Cost=104
> Card=18842 Bytes=263788)
> 8 2 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE) (Cost=1
> Card=287842 Bytes=1151368)
>
> ----------------------------------------------------------------------
> ********************************************************
> select count(p.personOID)
> from Address a, AddressXPerson axp, Person p
> where a.postalCode = '07010'
> OR postalCode = '07020'
> OR postalCode = '07022'
> OR postalCode = '07024'
> OR postalCode = '07030'
> OR postalCode = '07032'
> OR postalCode = '07047'
> AND axp.addressOID = a.addressOID
> AND axp.role = 'Practitioner'
> AND p.personOID = axp.personOID;
>
> Execution Plan
> ----------------------------------------------------------
> SELECT STATEMENT Optimizer=CHOOSE (Cost=1209441124 Card=1
> Bytes=27)
> 1 0 SORT (AGGREGATE)
> 2 1 CONCATENATION
> 3 2 NESTED LOOPS (Cost=1209441005 Card=2657453771070
> Bytes=71751251818890)
> 4 3 HASH JOIN (Cost=5120 Card=9232335 Bytes=212343705)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=24
> Card=49 Bytes=441)
> 6 5 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
> (NON-UNIQUE) (Cost=2 Card=49)
> 7 4 TABLE ACCESS (FULL) OF 'ADDRESSXPERSON' (Cost=104
> Card=188415 Bytes=2637810)
> 8 3 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE) (Cost=131
> Card=287842 Bytes=1151368)
> 9 2 NESTED LOOPS (Cost=1209441005 Card=2657453771070
> Bytes=71751251818890)
> 10 9 MERGE JOIN (CARTESIAN) (Cost=5120 Card=9232335
> Bytes=212343705)
> 11 10 INLIST ITERATOR
> 12 11 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'
> (Cost=24 Card=49 Bytes=441)
> 13 12 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
> (NON-UNIQUE) (Cost=2 Card=49)
> 14 10 SORT (JOIN) (Cost=5096 Card=188415 Bytes=2637810)
> 15 14 TABLE ACCESS (FULL) OF 'ADDRESSXPERSON' (Cost=104
> Card=188415 Bytes=2637810)
> 16 9 INDEX (FAST FULL SCAN) OF 'PERSON_PK' (UNIQUE)
> (Cost=131 Card=287842 Bytes=1151368)
>
> Thanks,

Doug

--
Posted via http://dbforums.com

Received on Mon May 19 2003 - 18:24:20 CDT