Re: Oracle does not choose proper Index

"Pritam" <pritamr_at_hotmail.com> wrote in message news:1121959828.521335.30930_at_g43g2000cwa.googlegroups.com...
> Jonathan,
>
> I am using Bind variable in where clause and Value of that bind
> variable is always 'I'. To make query simple, in my second post, I just
> submitted the value in place of bind variable.
>
> Query1 has 1 million rows satisfying the where clause but because of
> the FIRST_ROWS hint response is faster and returns result set fast
> (though it actually does not return the whole 1 million rows.)
>
> Query2 actually has only 2 rows satisfying the where clause. But since
> Oracle optimizer sorts the whole 1Million rows first and then filters,
> it is taking long time.
>
> I will try to create histogram on STATUS column and see what happens.
>
> Thanks.
>

Note that I said in my previous post:

<quote>

    If you are using literals, then this seems to be an ideal     case for creating a histogram on the status column so     that the optimizer had some information about the     relatively tiny number of rows with status = 'I' <end quote>

Since you are using bind variables, the histogram may either be pointless, or may cause problems.

Which version of Oracle are you using ? 9i will peek into the bind variable on the first parse of the statement, and use the value it finds there to optimize the statement. If you really do only use those query with the bind value set to 'I' then you can safely create a histogram (although this is a case where the use of literals would be good). If you execute the query with any other value in the bind variable, then it will execute with the plan created for whichever value was first used (if that plan is still in the shared pool).

The first_rows hint is deprecated in 9i, by the way. The closest equivalent is first_rows(1), although in the hint form any positive whole number is allowed in the brackets.

-- 
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