Re: Oracle does not choose proper Index

"Pritam" <pritamr_at_hotmail.com> wrote in message news:1121973882.721633.141500_at_g44g2000cwa.googlegroups.com...
> Jonathan,
>
> I created histogram on STATUS column and now it makes Query2
> fast(631msec) Only if I specify FIRST_ROWS(30) instead of FIRST_ROWS
> hint.
>
> Is it recommended to use FIRST_ROWS(N) instead of FIRST_ROWS?
>

Yes.

First_rows is deprecated in 9i.
It can cause problems because it
has some rules built into it that
push it very strongly to using
indexed access paths.

The first_rows(n) hint makes the
optimizer try to use some extra
arithmetic to decide whether or
not to use indexes.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005