Re: Density statistic calculation in case of histogram

From: Yong Huang <>
Date: Thu, 9 Apr 2009 13:46:45 -0700 (PDT)
Message-ID: <>

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, 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
           (h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
            and h.density*h.bucket_cnt*2 <= 1))
         then 'FREQUENCY'

In, 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       

Received on Thu Apr 09 2009 - 15:46:45 CDT

Original text of this message