Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Tuning a query in 734 rbo env. How does optimizer choose index?
If you are using 7.3.4, I don't recommend that you use the cost optimize
r
in a production environment.
You can check a SQL tuning tool, like Numega DBPartner (disclaimer: I work for Numega) that allows for RBO and CBO tuning.
One thing that you might do is to "disable" the other possible indexes
(like doing a dummy operation on the indexed cols, like indcol1 + 0 =
xxx
or indcolchar || '' = ...)
Fernando
>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
On 2/27/01, 10:16:33 PM, "John" <ebektech_at_yahoo.com> wrote regarding 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 n
o
> 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 th
em.
> Any other ideas?
> By the way what do you mean by "it chooses the index the index that ha
s
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 clau
se).
> > If two indexes seem to be the same, then (according to oracle suppor
t),
> > 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 option
s:
> >
> > 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. Th
is
rather
> > > complex query does an index range scan and then reads the table (v
ery
large)
> > > by rowid. This was taking forever so I created an index containin
g all
the
> > > columns to be read in the table (6 columns). This drastically imp
roved
the
> > > query performance as the optimizer only retrieved the information
from
the
> > > index, the query performance was then acceptable. Unfortunately a
fter I
> > > bounced the instance once, the optimizer didn't use this index an
ymore.
> > > Any ideas as to how I can force the optimizer to use a specific in
dex
> > > without going cost-based or specify hints? How does the optimizer
choose
> > > the index, does it use the one with the smallest index records thi
nking
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 m
atches
a
> > > certain criteria?
> >
Received on Fri Mar 09 2001 - 15:23:35 CST