Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle does not choose proper Index
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. Received on Thu Jul 21 2005 - 09:57:47 CDT