- change your Or to an 'IN'
- if Address has less records than the other tables(try it anyway)
make it a sub-query with an exists
where exists (select 1 from Address where blah...
- pr.remove_from_mdlocator <> 1
try to chnage that to an 'IN' not equals can be slow
- Possibly put Address and all its qualifiers in a sub-query with Exists.
"Doug" <dfult_at_econs.umass.edu> wrote in message
news:358a7114.0305281503.4043175d_at_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 - 19:16:34 CDT
