Brett C wrote:
>
> Greetings -
>
> We have a 7.3 server with the optimizer running in CHOOSE mode.
>
> We have a query in which four tables are joined. Two of the tables have
> statistics and two do not. Via an explain plan, we were able to determine that
> the two tables with statistics were using their indexes while the two without
> statistics were not. We ensured that the WHERE clause explicitly defined the
> columns comprising the indexes.
>
> The query ran horribly slow until we either changed the optimizer to run in
> RULES mode or placed a hint to use RULES mode on this query.
>
> Does anyone have any idea why this would happen ?
>
> Any assistance is appreciated.
>
> Brett
> zgeist.nospam_at_ee.net
If you don't use HINT or OPTIMIZER_MODE , then OPTIMIZER_MODE is CHOOSE.
In CHHOSE mode ALL_ROWS is used if any table in the query is analyzed.
ALL_ROWS tend to favour a table scan . This causes your problem in those
tables
Prab Athilat
Received on Thu Oct 30 1997 - 00:00:00 CST
