Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle does not choose proper Index

Re: Oracle does not choose proper Index

From: Pritam <pritamr_at_hotmail.com>
Date: 21 Jul 2005 07:57:47 -0700
Message-ID: <1121957867.222190.290330@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. Received on Thu Jul 21 2005 - 09:57:47 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US