Re: performance of OR versus IN

From: Doug <dfult_at_econs.umass.edu>
Date: 21 May 2003 14:40:55 -0700
Message-ID: <358a7114.0305211340.2a34c550@posting.google.com>

OK, I think I'm over the major brain damage that informed my original OR query. The performance of the IN query is still pretty poor and it still does a table scan of a reasonably large table even though an index is available.

Here's the query:

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 p.personOID = axp.personOID;

Here's the plan:

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=230 Card=1 Bytes=21) 1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=230 Card=97 Bytes=2037)
   3    2       HASH JOIN (Cost=133 Card=97 Bytes=1649)
   4    3         INLIST ITERATOR
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=2
          8 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 Ca
          rd=188431 Bytes=1507448)

   8    2       INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE) (Cost=1 Ca
          rd=287899 Bytes=1151596)

AddressXPerson has 188432 rows; it has an index on addressOID and I ran "analyze table AddressXPerson compute statistics" after adding the index.

Thoughts? Received on Wed May 21 2003 - 16:40:55 CDT