Re: Forsing certain execution without hints ...

In article <3A12896C.2EBE_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> sergey_s_at_my-deja.com wrote:
> >
> > What are some of the "tricks" that I could use to influence a
 certain
> > execution path in Oracle besides using the hints? I know of things
 like
> > appending a blank string ('') to avoid an index, or doing
> > something "dummy" on an indexed column to force the use of an index
> > (col > 0). What are some other things that can cause Oracle to use
> > indexes, or choose certain join methods, etc (besides the hints)?
> >
> > I have a group of Cognos users using the visual query builder. If I
> > edit the query manually (by adding hints) then Cognos disables the
> > visual query builder. Noone knows if there is a way to add hints
> > through the visual builder. However, we can add "tricks" in the
 where
> > or select clauses through the visual builder and not loose the
 ability
> > to use the visual builder the next time we get into Cognos. So, it's
> > all about the users being able to use their visual builder all the
 time
> > and at the same time being able to influence the best execution
 plan.
> >
> > Thank you!
> > Sergey
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> 1) order by indexed_col
>
> 2) using stored outlines
>
> HTH
> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Early to bed and early to rise,
> makes a man healthy, wealthy and wise." - some dead guy
>

Another hack I have used before in order to force oracle to always use indexes is the parameter:

optimizer_index_cost_adj

the value of this parameter tells the optimizer how favorable it should be towards usage of indexes. It can be set in the init.ora file or on a session by session basis using an alter session command. The value 1 is highly favorable to indexes. If indexes exist the optimizer will use them. If you have many indexes, however you run the problem of not knowing which index oracle is going to pick.

Hth

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 15 2000 - 17:38:41 CST