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

Thanks for your reply Myron. Unfortunately

> Change to cost based optimizer.

I would love too but I can't as it is one the custumer requirements to be in rbo.

> Change the session only into cost (or first_rows) optimizer mode.
I can't it's a third party application and this would affect other queries.

> Use a hint.

Using a hint causes the statement to be in cbo and because there are no stats the optimizer's plan is terrible. Once again the client doesn't want to
run stats.

> Use a full table scan and parallel query.
Unfortunately that table contains millions of rows and it still takes forever to do a full scan even in parallel.

> Remove all other indexes.

I'm going to review them tomorrow. I can't really remove any indexes as thousands of other queries are them but maybe I can combine some of them.

Any other ideas?

By the way what do you mean by "it chooses the index the index that has the highest block number for its extents"?

"Myron Wintonyk" <Myron.Wintonyk_at_UAlberta.CA> wrote in message news:3A9C576E.7F6A4AA_at_UAlberta.CA...
> 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 - 21:16:33 CST