On Mar 12, 4:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org
I'm DBA not developer, it seems that application cannot be changed, on
some installations
there are other values.
The problem is that i think that oracle does not make bind peeking
during hard parse,
i know that bind peeking occurs only first time.
If i user litteral values the execution plan is always optimal
I think that also index as that suggested by Daniel sometimes may
fail. If oracle
does Hard parse without bind peeking it does not now that value is <>
-1 and so
oracle choose to make the full table scan.
So i suspect that on eavy load Oracle during hard parse does not make
bind peeking
Received on Mon Mar 12 2007 - 12:02:48 CDT
