Re: query performance -- revised!

dfult_at_econs.umass.edu (Doug) 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)

I just realized something. you stated that as you add more 'OR' statements your query gets slower and slow. and you are forcing an index.....

is it just me or is that probably why this is running slower as the guy adds more values? use a full table scan and stick that in a sub-query with a where exists IF The Address table is smaller than the result you will get from the rest of the query without the Address table.

willing to be that is one of the biggest bottlenecks. should have been obvious.
wasnt thinking. Received on Thu May 29 2003 - 12:28:12 CDT