Re: Oracle not liking the index ... sometimes

Don

>Running Oracle 10.2.0.2.  A dev sent me a note that his java app was
>hanging.  Sure enough he had a query that was doing a full scan on a
>large table.  The problem was, his where clause was all equality lines
>on every field of the index that the query was supposed to use.  Stats
>on both the table and index were up-to-date (and re-gathered as I
>watched).

What datatypes is the java using? Inappropriate type conversion could cause the 
problem you see (eg I've just reproduced the same symptom by supplying a number 
to compare to compare with a number stored as a varchar2 in the database). Eg:


TEL_NUMBER is a VARCHAR2(10), and is the primary key for PHONE_DETAILS

SELECT * FROM PHONE_DETAILS WHERE PHONE_NUMBER = 123456789
- full table scan

SELECT * FROM PHONE_DETAILS WHERE PHONE_NUMBER = '123456789'
- index unique scan

Regards Nigel
--
http://www.freelists.org/webpage/oracle-l


Other related posts: