Re: Optimizer question in 7.3

From: Tony <trodgers_at_mindspring.com>
Date: 1997/10/30
Message-ID: <63ahun$93g@camel20.mindspring.com>#1/1

By default, CHOOSE (or CBO) mode typically uses the ALL_ROWS mode of the CBO. ALL_ROWS tends to favor full-table scans over indexes. You might try a hint of FIRST_ROWS in the query to see if that makes a difference. Also, you should have statistics collected for all your tables in the query.

Good luck!

Brett C wrote in message <63a6a8$kir_at_news1.ee.net>...
>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
>
Received on Thu Oct 30 1997 - 00:00:00 CST