query is running very slow if the index hint is not forced

I will analyze the index and see.
Thanks.

Quote:

---

Originally Posted by mike9

could you analyze the index an check if the optimzer choses then the correct index.
To analyze the indexes you could simply set cascade=true

DBMS_STATS.GATHER_TABLE_STATS ('quoteswp',p_tabname,'',estimate_percent=>10, degree=>4, cascade=>true
);

---

Mike, oracle should use the index when it returns less rows. Do you think so?.

I also analyzed the table with cascade=true. But still i got to force the index. Oracle choose the path based on the cost. Looks like some time it could go wrong... Seems like.

Tamil, I will find the trace file and i will take a look at it.

Thanks.

Quote:

---

Originally Posted by mike9

the reason why it choses the wrong index is because the optimizer thinks that he will get less rows returned with it

Code:

---

|*  3 |    INDEX RANGE SCAN          | VBAP_MATNR_IDX1  |  1251 |      |    9 |
 3 - access("VBAP"."MATNR"='000000000700203599')


|*  3 |    INDEX RANGE SCAN          | VBAP_VBELN_IDX1  |    44 |      |    3 |
 3 - access("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999')

---

---

which oracle version are you using?

how many rows ( count(*) ) do you got if you are only using the condtition
"VBAP"."MATNR"='000000000700203599'

how many rows ( count(*) ) do you got if you are only using the condtition
("VBAP"."VBELN">='0022000000' AND "VBAP"."VBELN"<='0022999999'