Re: Oracle not liking the index ... sometimes

From: Nigel Thomas <nigel_cl_thomas_at_yahoo.com>
Date: Tue, 3 Apr 2007 00:30:34 -0700 (PDT)
Message-ID: <75673.35662.qm@web58708.mail.re1.yahoo.com>

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

Received on Tue Apr 03 2007 - 02:30:34 CDT