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

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?
>
> Kind regards
>
> robert

Thanks for the query, I hadn't ran that test case just yet. Here's the results, they are close to what I was expecting.

Selected|Not selected
5888|493030

I did find a little more unexpected information in my inspecting though. There could be a small skew issue, as that column is supposed to be a primary key and it indeed has a PK constraint. But there are other types of records that are getting written into that table that do not use the same next number generator. This in itself is disturbing, but I'm trying to stay focused on the issue at hand. There are only 70 or so of these records that have been written this week out of 5000 total so I wouldn't think that the data is skewed enough to cause that issue.
here's a
select wadcto, count(*)
from proddta.f4801
group by wadcto.

A	1
EN	13359
ER	1
MR	1
OT	1
WA	738
WO	481789
WR	2730
WS	301

This shows the other type of records that are being written to the table. The only type that isn't using the same next number is the EN records. So out of almost 500000 records only 13359 wouldn't be evenly distributed in the wadoco column. They use a smaller number, so all of those records would appear on the left side of the index and the rest of the type's wadoco would be to their right. Received on Fri Jan 26 2007 - 09:24:25 CST