Re: query performance -- revised!

From: Mikito Harakiri <mikharakiri_at_ywho.com>
Date: Thu, 29 May 2003 10:17:21 -0700
Message-ID: <bnrBa.8$No4.136@news.oracle.com>

"Brian Peasland" <oracle_dba_at_remove_spam.peasland.com> wrote in message news:3ED6097F.A07F2C3F_at_remove_spam.peasland.com...
> >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.
>
> When you add more "OR" statements, you will be returning more rows from
> the table. At some point, the optimizer decides that a full table scan
> is preferrable to an index scan based on the percentage of rows
> returned.

There is certhainly a threshold where optimizer switches to FTS. The question might be if this switch happens too early. One way to check that is comparing estimated cardinalities at each rowsource against rows returned reportes as a part of execution statistics. If there is large discrepancy, then it is likely that optimizer decides switching to FTS too early in our case. Received on Thu May 29 2003 - 12:17:21 CDT