query performance

From: Doug <dfult_at_econs.umass.edu>
Date: 28 May 2003 15:29:06 -0700
Message-ID: <358a7114.0305281429.73dcee20@posting.google.com>

I am having trouble with a query that includes a bunch of ORs. My example, below, is with 25 ORs, but we have scenarios in the application with hundreds. It would be difficult to re-work the query, since it is created by code internal to an application server. But if need be, we will send raw sql, skipping the app server's adaptor. Note: I have a similar issue with IN if the number of elements gets large. In either case (OR or IN), there are NO table scans if the number of OR/IN elements is relatively small (seven, for example). I haven't tested the threshold, so far.

Anyway, any insights to the query and query plan, below?

Thanks,
Doug

SELECT count(pr.board_certification)
FROM Practitioner pr, AddressXPerson axp, Address a, OrganizationXPerson oxp, Person T1, Specialty s 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'
OR a.postalCode = '07071'
OR a.postalCode = '07072'
OR a.postalCode = '07073'
OR a.postalCode = '07074'
OR a.postalCode = '07086'
OR a.postalCode = '07087'
OR a.postalCode = '07093'
OR a.postalCode = '07094'
OR a.postalCode = '07096'
OR a.postalCode = '07097'
OR a.postalCode = '07302'
OR a.postalCode = '07303'
OR a.postalCode = '07304'
OR a.postalCode = '07305'
OR a.postalCode = '07306'
OR a.postalCode = '07307'
OR a.postalCode = '07308'
OR a.postalCode = '07309'
OR a.postalCode = '07310'

AND (oxp.role = 'Practitioner' AND axp.role = 'Location' AND a.country = 'USA' A
ND s.englishDescription = 'Urology' AND pr.remove_from_mdlocator <> 1)) AND T1.p
ersonOID = axp.personOID AND axp.addressOID = a.addressOID AND T1.personOID = ox
p.personOID AND pr.personOID = T1.personOID AND pr.specialtyOID = s.specialtyOID
;

Execution Plan

---

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=1 Bytes=74)    1 0 SORT (AGGREGATE)

   2    1     CONCATENATION
   3    2       NESTED LOOPS (Cost=757 Card=122737 Bytes=7486957)
   4    3         NESTED LOOPS
   5    4           NESTED LOOPS (Cost=755 Card=122738 Bytes=5523210)
   6    5             NESTED LOOPS (Cost=329 Card=172984

Bytes=2421776)

   7 6 NESTED LOOPS (Cost=479 Card=113219 Bytes=3170132)

   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (
           Cost=93 Card=201 Bytes=2613)

   9    8                   INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
           (NON-UNIQUE) (Cost=2 Card=201)

  10    7                 TABLE ACCESS (BY INDEX ROWID) OF
'ADDRESSXPERSON' (Cost=104
            Card=188431 Bytes=2638034)

  11   10                   INDEX (RANGE SCAN) OF

'ADDRESSXPERSON_ADDRESSOID' (NON-UNIQUE)   12 6 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE)
(Cost=131 Card=287899

            Bytes=1151596)

  13 5 TABLE ACCESS (BY INDEX ROWID) OF 'ORGANIZATIONXPERSON' (Cost=220

           Card=312104 Bytes=5305768)

  14 13 INDEX (RANGE SCAN) OF 'ORGANIZATIONXPERSON_PERSON' (NON-UNIQUE)   15 4 TABLE ACCESS (BY INDEX ROWID) OF 'PRACTITIONER'
(Cost=160 Card=172984

           Bytes=1729840)

  16 15 INDEX (UNIQUE SCAN) OF 'PRACTITIONER_PK'
(UNIQUE)
  17 3 TABLE ACCESS (BY INDEX ROWID) OF 'SPECIALTY' (Cost=1 Card=37 Bytes=592)

  18   17           INDEX (UNIQUE SCAN) OF 'SPECIALTY_PK' (UNIQUE)
  19    2       HASH JOIN (Cost=851 Card=222 Bytes=16428)
  20   19         INLIST ITERATOR
  21   20           TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS'

(Cost=93 Card=201 Bytes=2613)

  22 21 INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'
(NON-UNIQUE) (Cost=2

            Card=201)

  23   19         HASH JOIN (Cost=757 Card=122737 Bytes=7486957)
  24   23           TABLE ACCESS (FULL) OF 'SPECIALTY' (Cost=1 Card=37

Bytes=592)

  25   23           HASH JOIN (Cost=755 Card=122738 Bytes=5523210)
  26   25             HASH JOIN (Cost=479 Card=113219 Bytes=3170132)
  27   26               HASH JOIN (Cost=329 Card=172984 Bytes=2421776)
  28   27                 TABLE ACCESS (FULL) OF 'PRACTITIONER'


(Cost=160 Card=172984

            Bytes=1729840)

  29   27                 INDEX (FAST FULL SCAN) OF 'PERSON_PK'


(UNIQUE) (Cost=131 Card=287899

            Bytes=1151596)

  30   26               TABLE ACCESS (FULL) OF 'ADDRESSXPERSON'


(Cost=104 Card=188431

            Bytes=2638034)

  31   25             TABLE ACCESS (FULL) OF 'ORGANIZATIONXPERSON'


(Cost=220 Card=312104

            Bytes=5305768)

Received on Wed May 28 2003 - 17:29:06 CDT