Re: query performance -- another try

"Doug" <dfult_at_econs.umass.edu> wrote in message news:358a7114.0306041503.521ecdb3_at_posting.google.com...
> I used alter session to set optimizer_index_caching=99 and
> optimizer_index_cost_adj=10 (based on some other posts).
>
> The simple query now uses the index in both cases (42 OR clauses or 43
> OR clauses), but the time difference is still present (for one
> additional OR clause) -- .31 to 3.31.

The buffer gets count increased 3 times! I don't understand why. In 9.2 we could have exactly pinpoint the rowsource where it happened...

> If I ramp the number of OR
> clauses up (in practice, we can have over a thousand -- this is a
> postalCode proximity thing), the time continues to degrade pretty
> badly, e.g. 27 seconds for 325 OR clauses, using the index. Anyway,
> the crux of it seems to lie in the big difference between the two
> threshold cases (threshold in the sense that 43 OR clauses was where
> it used to switch to a table scan before I altered the session
> parameters as described above).
>
> One more wrinkle: I dropped the "AND country = 'USA'" from the query
> and the time difference vanished.

If your query just counts the rows, then execution plan uses index only and skips table access operation altogether. Therefore, it looks like those excessive buffer gets are coming from table access by index...

> It suggests that I should add
> Country to the index => (postalCode, country), but it still doesn't
> explain why the huge time difference for one additional OR clause.
Received on Wed Jun 04 2003 - 19:34:12 CDT