performance of OR versus IN

From: Doug <dfult_at_econs.umass.edu>
Date: 19 May 2003 15:24:38 -0700
Message-ID: <358a7114.0305191424.614445a4@posting.google.com>

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

---

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

---

   0 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 Received on Mon May 19 2003 - 17:24:38 CDT