Re: Bind Peeking with java application

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