Re: Density statistic calculation in case of histogram

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 Huang       

--
http://www.freelists.org/webpage/oracle-l