Re: query performance -- another try

"Doug" <dfult_at_econs.umass.edu> wrote in message news:358a7114.0306021313.3be7b66f_at_posting.google.com...
> 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)

We knew that the plan would switch like this at some threshold already (Cost=154->155 in your case). What we don't know is the runtime performance difference between the 2 cases. And the number of rows processed at each rowsource (to compare those at the estimated cardinalities). This is why I asked tkprof output.

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

Therefore, the column is highly skewed. Please, doublecheck if you have the histogram.

> "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.
Received on Mon Jun 02 2003 - 16:53:54 CDT