Re: performance of OR versus IN

From: Nuno Souto <wizofoz2k_at_yahoo.com.au>
Date: 19 May 2003 19:51:53 -0700
Message-ID: <73e20c6c.0305191851.1fd271ce@posting.google.com>

dfult_at_econs.umass.edu (Doug) wrote in message news:<358a7114.0305191424.614445a4_at_posting.google.com>...

> 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
> ----------------------------------------------------------
> 0 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)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

this is bad. You sure addressOID in AXP is indexed? If not, that is the reason.

> select count(p.personOID)
> from Address a, AddressXPerson axp, Person p
<Try replacing this:>
> where a.postalCode = '07010'
> OR postalCode = '07020'
> OR postalCode = '07022'
> OR postalCode = '07024'
> OR postalCode = '07030'
> OR postalCode = '07032'
> OR postalCode = '07047'

<with this:>
> 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')

<end replacing, note the aliases and parenthesis!>
> AND axp.addressOID = a.addressOID
> AND axp.role = 'Practitioner'
> AND p.personOID = axp.personOID;

> snip...
> 10 9 MERGE JOIN (CARTESIAN) (Cost=5120 Card=9232335
> Bytes=212343705)

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is truly horrible...
If the above changes don't make it, you may need hints or fiddling with stats.

Of course, make sure you analyze fresh.

HTH
Cheers
Nuno Souto
wizofoz2k_at_yahoo.com.au.nospam Received on Mon May 19 2003 - 21:51:53 CDT