Re: Density statistic calculation in case of histogram
Date: Thu, 9 Apr 2009 13:46:45 -0700 (PDT)
Alberto's article is very interesting. It looks like Oracle keeps making changes to histogram algorithm. Not answering Neeraj's question. I want to point out that even the type, height-balanced vs frequency, is determined differently in different versions. Between 10gR1 and 10.2.0.3, the type is determined by
SQL> select text from dba_views where view_name = 'ALL_TAB_COLS';
case when nvl(h.row_cnt,0) = 0 then 'NONE'
when (h.bucket_cnt > 255 or (h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt and h.density*h.bucket_cnt*2 <= 1)) then 'FREQUENCY' else 'HEIGHT BALANCED'
In 10.2.0.4, the "*2" bit on the 5th line is dropped. In 11gR1, "<=" changes to "<" (in addition to the drop of "*2"). We normally determine the type by the difference between bucket count and distinct value count. But the above formula tells us there may be some more criteria. Bucket_cnt>255 is not possible; it's there for historical reason? Row_cnt=distcnt, and the density vs bucket_cnt comparison? And how can density become part of type determination? It would cause density calculation to be postponed till the type has been determined, causing circular logic.
Yong HuangReceived on Thu Apr 09 2009 - 15:46:45 CDT