Re: Oracle not liking the index ... sometimes
- From: Nigel Thomas <nigel_cl_thomas@xxxxxxxxx>
- To: don@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Apr 2007 00:30:34 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
