Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> query performance -- revised!
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'Received on Wed May 28 2003 - 18:03:38 CDT
(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)