Re: query performance -- another try

I simplified the query as suggested and here is the two versions of the plan:

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=154 Card=1 Bytes=9)    1 0 SORT (AGGREGATE)

   2    1     INLIST ITERATOR
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESS' (Cost=154 C
          ard=3 Bytes=27)

   4    3         INDEX (RANGE SCAN) OF 'ADDRESS_POSTALCODE' (NON-UNIQ
          UE) (Cost=2 Card=3)

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=155 Card=1 Bytes=9)    1 0 SORT (AGGREGATE)

   2    1     TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=155 Card=3 Bytes=
          27)

There are 119 different values for country (including 815 nulls); the vast majority 110K have country = "USA".

"Mikito Harakiri" <mikharakiri_at_ywho.com> wrote in message news:<FetBa.10$No4.124_at_news.oracle.com>...
> First, you can reduce the case significantly.
>
> SELECT count(1)
> FROM Address a
> where ((a.postalCode = '07010'
> OR a.postalCode = '07020'
> OR a.postalCode = '07022'
> ...
> OR a.postalCode = '10007'
> ) and a.country = 'USA'
>
> Similar to your original case, optimiser would switch to FTS in this reduced
> case as well.
>
> Next, please, run the query in both cases -- with long OR-predicates list,
> and with short list -- and let us know what tkprof says. Also please make
> sure that you have a histogram on country if you have more than one value
> there.
>
> "Doug" <dfult_at_econs.umass.edu> wrote in message
> news:358a7114.0305291044.597af502_at_posting.google.com...
> > First, thanks for your patience and comments to date.
> >
> > The sql below is being generated in an application server environment,
> > so I am hoping to find a performance improvement without altering the
> > sql -- though I am open to any suggestions.
> >
> > I explained the situation in previous posts, but did a miserable job
> > with providing the particular query plans that went with the
> > performance problem (not to mention a big problem with parentheses),
> > so let me try again. (My only excuse is, as Willy Wonka said, "So
> > much time, so little to do ...." I know you all have better things to
> > do than wade through my mistakes; I believe I've got it right this
> > time.)
> >
> > With many OR clauses (> 42 in my case), the optimizer (cost-based, I
> > believe), chooses to use a table scan of a table (Address) with
> > 113,524 records -- 14,115 distinct values for the indexed column,
> > postalCode. Below is the query and then the two plans (before the
> > threshold number of OR clauses and after -- notice the table scan of
> > Address in the latter). (Note: a similar situation arises with IN and
> > I am trying to avoid IN since the max number of elements is 1000 and
> > we sometimes end up with more than that in the query -- it's a
> > zipcodes-within-a-certain-proximity sort of thing.)
> >
> > (Oracle Enterprice Edition, 8.1.6)
> >
> > SELECT count(pr.board_certification)
> > FROM Practitioner pr, AddressXPerson axp, Address a,
> > OrganizationXPerson oxp, Person 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' AND s.englishDescription = 'Urology' AND
> > pr.remove_from_mdlocator <>
> > 1)) AND T1.personOID = axp.personOID AND axp.addressOID = a.addressOID
> > AND
> > T1.personOID = oxp.personOID AND pr.personOID = T1.personOID AND
> > pr.specialtyOID =
> > s.specialtyOID
> > ;
> >
> > Before threshold:
> >
> > 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)
> >
> >
> > *******
> >
> > After threshold:
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=175 Card=1 Bytes=74)
> > 1 0 SORT (AGGREGATE)
> > 2 1 NESTED LOOPS (Cost=175 Card=1 Bytes=74)
> > 3 2 NESTED LOOPS (Cost=172 Card=1 Bytes=57)
> > 4 3 NESTED LOOPS (Cost=171 Card=1 Bytes=41)
> > 5 4 NESTED LOOPS (Cost=167 Card=4 Bytes=124)
> > 6 5 NESTED LOOPS (Cost=163 Card=4 Bytes=108)
> > 7 6 TABLE ACCESS (FULL) OF 'ADDRESS' (Cost=155 Car
> > d=4 Bytes=52)
> >
> > 8 6 TABLE ACCESS (BY INDEX ROWID) OF 'ADDRESSXPERS
> > ON' (Cost=2 Card=18844 Bytes=263816)
> >
> > 9 8 INDEX (RANGE SCAN) OF 'ADDRESSXPERSON_ADDRES
> > SOID' (NON-UNIQUE) (Cost=1 Card=18844)
> >
> > 10 5 INDEX (UNIQUE SCAN) OF 'PERSON_PK' (UNIQUE) (Cos
> > t=1 Card=287899 Bytes=1151596)
> >
> > 11 4 TABLE ACCESS (BY INDEX ROWID) OF 'PRACTITIONER' (C
> > ost=1 Card=86492 Bytes=864920)
> >
> > 12 11 INDEX (UNIQUE SCAN) OF 'PRACTITIONER_PK' (UNIQUE
> > )
> >
> > 13 3 TABLE ACCESS (BY INDEX ROWID) OF 'SPECIALTY' (Cost=1
> > Card=1 Bytes=16)
> >
> > 14 13 INDEX (UNIQUE SCAN) OF 'SPECIALTY_PK' (UNIQUE)
> > 15 2 TABLE ACCESS (BY INDEX ROWID) OF 'ORGANIZATIONXPERSON'
> > (Cost=3 Card=26009 Bytes=442153)
> >
> > 16 15 INDEX (RANGE SCAN) OF 'ORGANIZATIONXPERSON_PERSON' (
> > NON-UNIQUE) (Cost=2 Card=26009)
Received on Mon Jun 02 2003 - 16:13:21 CDT