Re: Oracle does not choose proper Index

"Pritam" <pritamr_at_hotmail.com> wrote:
> 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.

Oracle is not sorting the whole 1 million rows first. The only reason it would be use the index on col2 is to that it can avoid sorting, by simply accessing the rows in col2 order in the first place.

> I will try to create histogram on STATUS column and see what happens.

If you are using bind variables, it won't help. But if the bind value is always 'I', then there is little reason to use bind variables.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB