| 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'
(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
![]() |
![]() |