Re: Cost Based Optimizer - Chooses slowest plan?

Sometimes the cost based analyzer choses poorly. The plan with the lowest cost is not always the fastest. In these cases you can use the 'RULE' hint to override the cost based analyzer. For Example:

select /* RULE */
emp_no
from emp
where emp_no > 20;

You can also force it to use an index using the index hint. The following example will force the use of an index from the company table.

select /*+ INDEX(COMPANY) */
  name, city, state
from company
where city = 'Boston'
and State = 'MA';

CJ <djjr_at_ix.netcom.com> wrote in message news:37C1C28E.CC65764E_at_ix.netcom.com...
> I'm running a 7.3.4 database in choose mode. At one point the optimizer
> chose to use indexes for my sql which
> I know executes faster than doing a full table scan. I've actully seen
> the performance degredation when I run it
> with out using the indexes. Now its chose not to use indexes. The cost
> for the sql using the index is actually higher than the sql not using
> the index
> even though the sql using the index is faster. I thought the fastest
> sql would also have the lowest cost? I've analyzed
> the tables and indexes in question, so the statistics are there. Are
> there problems with the optimizer in 7.3.4? Are there
> additional parameters that need to be set for the optimizer to choose a
> better(faster) plan?
>
> TIA
>
Received on Wed Aug 25 1999 - 11:11:18 CDT