Cristian Cudizio wrote:
> 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
I think you are deriving conclusions for which I can not think of
any supporting information and are fixated on bind peeking when it
is not necessarily relevant to the issue.
And your statement "I think that also index as that suggested by Daniel
sometimes may fail." ridiculous but I am willing to listen to why you
think this is the case.
I suggest that you stop looking at a specific issue/solution and focus
on the business problem. What is it?
--
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 - 17:07:43 CDT
