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

Home -> Community -> Usenet -> c.d.o.server -> Re: Bind Peeking with java application

Re: Bind Peeking with java application

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 12 Mar 2007 08:46:43 -0700
Message-ID: <1173714403.371776@bubbleator.drizzle.com>


Cristian Cudizio wrote:
> Hello,
> we have a strange problem. We have a java application that make a
> query like this:
> select * from table t where col1 = :bind1
> Were bind value is a number > 0.
> Table t has over 20 milion records, all record have value -1 for
> column col1. That means
> that above query returns always 0 record (why this i a long history).
> We hava an installation on Oracle 10.1.0.3 and 10.1.0.5 that has
> always worked correctly,
> with an index on column col1 that means fast.
> With another installation on Oracle db 10.2.0.2, with default
> statistics
> (on col1 there are histograms calculated). Sometimes Oracle makes a
> full table scan on table t.
> It suffices to do a "alter system flush shared pool" that Oracle
> recalculates the correct
> execution plan using index on column col1. This is like Oracle does
> not make Bind peeking, in fact,
> an explain plan with bind variable makes a full scan on table t, but
> a real execution plan with
> a bind value uses the indexes.
> Is possible that randomly, sometimes Oracle does not make BIND
> PEEKING ?
> Programmers have assured me that variable ":bind1" is never -1 that is
> value not selective, they
> assured me that ":bind1" is always > 0 that is a value highly
> selective. So the only
> answer for me i that Oracle sometimes does not make bind peeking. Is
> that possible, have
> somebody heard about a bug?

Other than the obvious bad design why not do this?

CREATE INDEX <index_name>
ON <table_name> (DECODE(col1, -1, NULL, col1);

If the value is always -1 Oracle is never going to use the index to find a value of -1 so it is irrelevant that the value is not indexed. The resulting index will be very small and contain every non -1 value: According to you none at all.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Mon Mar 12 2007 - 10:46:43 CDT

Original text of this message

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