Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> query performance -- revised!

query performance -- revised!

From: Doug <dfult_at_econs.umass.edu>
Date: 28 May 2003 16:03:38 -0700
Message-ID: <358a7114.0305281503.4043175d@posting.google.com>


Apologies for the last post (query performance) -- especially because I made the mistake before. I have corrected my syntax and there is still a threshold (43 ORs) at which it switches from using an index to using a table scan on the Address table. I am suspicious of its decision because our previous db server (OpenBase) ran this query considerably faster, even with hundreds of ORs. Query and query plan:

SELECT /*+ INDEX(Address address_postalCode) */ count(pr.board_certification)
FROM Practitioner pr, AddressXPerson axp, Address a, OrganizationXPerson oxp, Pe
rson T1, Specialty s
where ((a.postalCode = '07010'
OR a.postalCode = '07020'
OR a.postalCode = '07022'
...
OR a.postalCode = '10007'
)
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=170 Card=1 Bytes=74)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=170 Card=1 Bytes=74)
   3    2       NESTED LOOPS (Cost=167 Card=1 Bytes=57)
   4    3         NESTED LOOPS (Cost=166 Card=1 Bytes=41)
   5    4           NESTED LOOPS (Cost=163 Card=3 Bytes=93)
   6    5             NESTED LOOPS (Cost=160 Card=3 Bytes=81)
   7    6               INLIST ITERATOR
   8    7                 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (
          Cost=154 Card=3 Bytes=39)

   9    8                   INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE'

(NON-UNIQUE) (Cost=2 Card=3)
10 6 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSXPERS ON' (Cost=2 Card=18844 Bytes=263816) 11 10 INDEX (RANGE SCAN) OF 'ADDRESSXPERSON_ADDRES SOID' (NON-UNIQUE) (Cost=1 Card=18844) 12 5 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE) (Cos t=1 Card=287899 Bytes=1151596) 13 4 TABLE ACCESS (BY INDEX ROWID) OF 'PRACTITIONER' (C ost=1 Card=86492 Bytes=864920) 14 13 INDEX (UNIQUE SCAN) OF 'PRACTITIONER_PK' (UNIQUE ) 15 3 TABLE ACCESS (BY INDEX ROWID) OF 'SPECIALTY' (Cost=1 Card=1 Bytes=16) 16 15 INDEX (UNIQUE SCAN) OF 'SPECIALTY_PK' (UNIQUE) 17 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORGANIZATIONXPERSON'
(Cost=3 Card=26009 Bytes=442153)
18 17 INDEX (RANGE SCAN) OF 'ORGANIZATIONXPERSON_PERSON' ( NON-UNIQUE) (Cost=2 Card=26009)
Received on Wed May 28 2003 - 18:03:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US