Re: 900 distinct values, yet density yields 0.5??

From: <hansdegit_at_hotmail.com>
Date: 4 Mar 2006 05:59:31 -0800
Message-ID: <1141480771.204020.21710@i40g2000cwc.googlegroups.com>

Jonathan Lewis schreef:

> Is this a repeatable phenomenon, or just a one-off
> oddity ? Is it possible that someone has been playing
> with dbms_stats.set_columns_stats ?
>

I was hoping you would notice this thread, Jonathan. Thanks for taking the time to reply.

Actually, this is the case: when I write a procedure that includes the abovementioned call to dbms_stats and schedule it using dbms_job, the density columns gets updated with 0.3333333. Every time.

When I supply my own choice of sample_size, density equals 1/ndv. Oracle does not manage to accurately estimate num_distinct, though. Perhaps that is because of the fact that the column process_instance is incremented with every batch.

Using dbms_stats.auto_sample_size however, density equals 0.333333. I must be hitting a bug, so I opened an SR on Metalink. I'll keep you updated on this.

It's a shame that Oracle uses density in its calculations in stead of num_distinct. Received on Sat Mar 04 2006 - 07:59:31 CST