Re: Distinct values
Date: Thu, 5 Aug 2010 02:24:12 -0700 (PDT)
Message-ID: <ace8301d-934b-4efb-996d-6e115a8b0ff2_at_z10g2000yqb.googlegroups.com>
On Aug 4, 6:17 pm, Mladen Gogala <n..._at_email.here.invalid> wrote:
> Thanks! I didn't know that Joze had a blog and we have worked together in
> the past. Joze Senegacnik is a great guy, a member of the furniture store
> named "Oak Table" or something like that. Apparently, Joze did a bit
> more disciplined and systematic testing than me. This, however, is an
> interesting anomaly. BTW, I got Tom's book, I am busy reading it, so I
> may miss an interesting blog or two or even some interesting threads on
> this group.
Mladen,
you've used the ANALYZE command for generating CBO statistics that has been deprecated a long time ago for that purpose:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/statements_4005.htm#SQLRF01105
DBMS_STATS has similar issues but is less limited than ANALYZE.
In particular the NUM_DISTINCT information will be correct with DBMS_STATS even for strings longer than 32 bytes as long as you don't generate histograms (SIZE 1 vs. SIZE > 1), whereas ANALYZE has this 32 bytes limitation regardless of the histogram generation.
I've done some time ago a general discussion on the limitations of histograms with DBMS_STATS, since the issue is not limited to character columns:
http://oracle-randolf.blogspot.com/2009/01/limitations-of-histograms-rounding.html
The blog post doesn't cover ANALYZE and it doesn't mention above point that DBMS_STATS does the NUM_DISTINCT correct when not generating a histogram.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Thu Aug 05 2010 - 04:24:12 CDT