分析的桶数多于唯一个数，为什么HISTOGRAM=HEIGHT BALANCE?

逆袭的W

学习学习~

Yong Huang

原帖由 棉花糖ONE 于 2009-12-28 19:30 发表
那地方虽然显示的是基于高度的直方图,实际上应该是基于频率的,只是h.bucket_cnt > h.distcnt 把那个过滤,我觉得USER_TAB_COL_STATISTICS 的定义里应该把h.bucket_cnt > h.distcnt 改成h.bucket_cnt > =h.distcnt 更合适

I realize that ZALBB's test and mine later are very special in that there're only a few values in total, each distinct. ZALBB, you didn't say but I'm sure you only have 6 values, each distinct from each other. Correct? In this special case, if you specify more buckets than number of (distinct) values, Oracle will create only that many buckets, each containing one value which is distinct. *In this case, the definition of both frequency (FH) and height-balanced histogram (HBH) applies!* You can tell from the endpoint_number column of user_tab_histogram. If the numbers in this column represent the ordinal numbers of the buckets (i.e., starting from 1, then 2, 3, etc), it's an HBH. If they represent cumulative counts over previous buckets (or rows in this view query output), it's an FH. But in this case, it's both.

My conclusion is that this special histogram can be called either type. So whether you change > to >= in the view definition as you suggested makes no difference.

Yong Huang

ZALBB

原帖由 Yong Huang 于 2009-12-30 02:26 发表


I realize that ZALBB's test and mine later are very special in that there're only a few values in total, each distinct. ZALBB, you didn't say but I'm sure you only have 6 values, each distinct from each other. Correct? In this special case, if you specify more buckets than number of (distinct) values, Oracle will create only that many buckets, each containing one value which is distinct. *In this case, the definition of both frequency (FH) and height-balanced histogram (HBH) applies!* You can tell from the endpoint_number column of user_tab_histogram. If the numbers in this column represent the ordinal numbers of the buckets (i.e., starting from 1, then 2, 3, etc), it's an HBH. If they represent cumulative counts over previous buckets (or rows in this view query output), it's an FH. But in this case, it's both.

My conclusion is that this special histogram can be called either type. So whether you change > to >= in the view definition as you suggested makes no difference.

Yong Huang

比较文档定义，这应该是个BUG。

ZALBB

咨询了ORACLE技术支持，答复是Bug 4704779，但据他们说，此BUG已经在10204版本上修复。但我的问题
恰恰就是在10204上出现的。

Yong Huang

原帖由 ZALBB 于 2009-12-30 19:32 发表
咨询了ORACLE技术支持，答复是Bug 4704779，但据他们说，此BUG已经在10204版本上修复。但我的问题
恰恰就是在10204上出现的。

I don't think that bug is relevant, although on the surface it appears to be. I'm willing to say it may be a documentation bug in that the documentation says that as long as you speficy a bucket count greater than the number of distinct values, you get a frequency histogram. That's obviously incorrect.

And that's an issue different from the special case histogram, which I personally believe is both a frequency and height-balanced type, because it fits both definitions.

Yong Huang