Re: Tuning a query in 734 rbo env. How does optimizer choose index?

Oracle tries to determine which index is better (from the where clause). If two indexes seem to be the same, then (according to oracle support), it chooses the index the index that has the highest block number for its extents (I don't necessarily believe this description, but it doesn't matter to me).

So, from your perspective it's random and can change with or without reboots.

I realize you said RULE and no hints, but here are some other options:

    Change to cost based optimizer.
    Change the session only into cost (or first_rows) optimizer mode.     Use a hint.
    Use a full table scan and parallel query.     Remove all other indexes.

Good luck!

ebektech wrote:

> I am trying to tune a query in a 7.3.4 Rule-based environment. This rather
> complex query does an index range scan and then reads the table (very large)
> by rowid. This was taking forever so I created an index containing all the
> columns to be read in the table (6 columns). This drastically improved the
> query performance as the optimizer only retrieved the information from the
> index, the query performance was then acceptable. Unfortunately after I
> bounced the instance once, the optimizer didn't use this index anymore.
> Any ideas as to how I can force the optimizer to use a specific index
> without going cost-based or specify hints? How does the optimizer choose
> the index, does it use the one with the smallest index records thinking it
> would be more efficient? Or does it simply use the most recently used one?
> Or does it simply use the first one it finds in its catalog that matches a
> certain criteria?
  Received on Tue Feb 27 2001 - 19:42:06 CST