Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle not liking the index ... sometimes
The indexed columns and their Oracle and Java types (that his app is
using) are as follows:
LOC: Oracle CHAR(3), Java String
CYC_YEAR: Oracle NUMBER(8), Java Long
CYC: Oracle NUMBER(4), Java Long
CYC_END_DAY: Oracle CHAR(2), Java String
SERVICE_MIN: Oracle VARCHAR2(15), Java String
SVC_TYPE: Oracle CHAR(1), Java String
As I said earlier, when I changed from using Long to String, performance actually got worse in his app, even though my Oracle SQL Dev sends all VARCHAR2 anyway. There was no change when he changed from Long to Integer.
The index was created just for this query, and is comprised of only these six fields.
Don.
On 4/3/07, Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:
> At 09:32 AM 4/3/2007, Don Seiler wrote:
> >Alright this gets even more confusing (to me anyway). I looked in
> >v$sql_bind_capture to see what the datatypes were. When he was
> >running via his java app, it was actually using the right datatypes, 3
> >varchar2 and 2 numbers.
> >
> >However, looking at my tests from Oracle SQL Developer, it actually
> >passes in all varchar2(32) types, and uses the index. Pretty
> >counterintuitive.
>
> That suggests that there is a type mismatch somewhere which prevents
> the optimizer from using the index.
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
>
>
>
> ______________________________________________________________________
> This email has been scanned by the MessageLabs Email Security System.
> For more information please visit http://www.messagelabs.com/email
> ______________________________________________________________________
>
-- Don Seiler http://seilerwerks.blogspot.com -- http://www.freelists.org/webpage/oracle-lReceived on Tue Apr 03 2007 - 12:50:38 CDT
![]() |
![]() |