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

On Jan 26, 12:04 pm, lothar.armbrues..._at_t-online.de (Lothar Armbrüster) wrote:
> "Ben" <bal..._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.armbrues..._at_t-online.de
> Hauptstr. 26 |
> 65346 Eltville |- Hide quoted text -- Show quoted text -

I thought about that earlier but, yeah, I'm sure it's a number field.

select owner, table_name, column_name, data_type from dba_tab_cols
where owner = 'PRODDTA'
and table_name = 'F4801'
and column_name = 'WADOCO'

OWNER|TABLE_NAME|COLUMN_NAME|DATA_TYPE
PRODDTA|F4801|WADOCO|NUMBER That and the fact that the explain plan suggested a full scan when I substituted the actual number value instead of a bind variable. It's like the cost of using the index is inaccurate. I don't know how to fix it or how it got that way.

I guess my next step is going to be to export the stats table and then export the current stats for that table using a statid and then reimport the old stats for that table to see if it the path has changed. My guess is yes, but there isn't really a good explanation why. Received on Fri Jan 26 2007 - 12:10:33 CST