Re: Oracle does not choose proper Index

"Pritam" <pritamr_at_hotmail.com> wrote in message news:1121957867.222190.290330_at_g44g2000cwa.googlegroups.com...
> Here is the Query1 ( takes 120msec)
> Select /*+FIRST_ROWS*/ KEMCLAIMDETAIL.CLAIM, KEMCLAIMDETAIL.STATUS
>>From KEMCLAIMDETAIL
> Where NOT(KEMCLAIMDETAIL.STATUS='I')
> Order By KEMCLAIMDETAIL.CLAIM
>
> Explain Plan:
> Operation Object Name Rows Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=HINT: FIRST_ROWS 751 K 826
> TABLE ACCESS BY INDEX ROWID KEMCLAIMDETAIL 751 K 14 M 826
> INDEX FULL SCAN KEMCLAIMDETAIL1 1 M 26
>
>
> Here is the Query2 ( takes 1min 15sec)
> Select /*+FIRST_ROWS*/ KEMCLAIMDETAIL.CLAIM, KEMCLAIMDETAIL.STATUS
>>From KEMCLAIMDETAIL
> Where KEMCLAIMDETAIL.STATUS='I'
> Order By KEMCLAIMDETAIL.CLAIM
>
> Explain Plan:
> Operation Object Name Rows Bytes Cost
>
> SELECT STATEMENT Optimizer Mode=HINT: FIRST_ROWS 375 K 826
> TABLE ACCESS BY INDEX ROWID KEMCLAIMDETAIL 375 K 7 M 826
> INDEX FULL SCAN KEMCLAIMDETAIL1 1 M 26
>
>
> Only difference in 2 queries is the where clause.
> Also STATUS column only has 4 distinct values (I, F, O, R)
>
> Let me know if you need any more information.
> Thanks.
>

In your earlier post, you indicated that you were using bind variables, that query one returned 1M rows and that query 2 returned 2 rows.

In this post you show you are using literal value for the predicates - and say that query one returns in 120 milliseconds.

Is it possibly that the 120 milliseconds is the time to return the first couple of rows, rather than the full 1M rows ?

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'

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