Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Oracle not liking the index ... sometimes

Re: Oracle not liking the index ... sometimes

From: Don Seiler <don_at_seiler.us>
Date: Tue, 3 Apr 2007 12:50:38 -0500
Message-ID: <716f7a630704031050q4476eceaxc7e6a0bc316e88dd@mail.gmail.com>


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-l
Received on Tue Apr 03 2007 - 12:50:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US