Re: Unique index not being used, up to date stats

From: Lothar Armbrüster <lothar.armbruester_at_t-online.de>
Date: Fri, 26 Jan 2007 18:04:04 +0100
Message-ID: <87iretzpmj.fsf@prometeus.none.local>

"Ben" <balvey_at_comcast.net> writes:

> On Jan 26, 10:07 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:

>> On 26.01.2007 16:02, Ben wrote:
>>
>> > Yeah, I use
>> > dbms_stats.gather_schema_stats(
>> > ownname => 'PRODDTA',
>> > cascade => true,
>> > degree => 6,
>> > options => 'GATHER STALE');
>>
>> > This runs every weekend, and the table in question & indexes were
>> > analyzed this past weekend.One more idea: did you verify that the data actually meets your
>> expectations? You could do
>>
>> select sum(case when wadoco >= 11723420 then 1 else 0 end) "selected"
>> , sum(case when wadoco >= 11723420 then 0 else 1 end) "not selected"
>> FROM proddta.f4801
>>
>> to make sure that your idea of the data matches reality.
>>
>> Are there any stored outlines (stored plans) around?
>>

[...]

Hello Ben,

I just want to add my wild guess here.
Are you sure that wadoco is a number column? The behavior you describe sounds like looking up numeric values in a varchar2 column. When you use bind variables in explain it assumes the right data type but giving numeric literals doesn't use the index. When using bind variables from application you can set the data type of these to numeric so that the index isn't used either.

Hope that helps,
Lothar

-- 
Lothar Armbrüster  | lothar.armbruester_at_t-online.de
Hauptstr. 26       |
65346 Eltville     |

Received on Fri Jan 26 2007 - 11:04:04 CST