信息来源: it168
在10gR1开始,oracle就允许用户自己构造柱状图,由于柱状图分等高与等宽两种,所以由width_bucket构造等宽柱状图,由ntile构造等高的柱状图,测试方法如下所示:
创建表:
createtable test_0211 asselect*from dba_objects;
分析并产生柱状图:
analyze table test_0211 computestatisticsfortableforall columns size 18;
查看已经产生的柱状图:
SQL> col endpoint_value format 999999
SQL> col column_name format a18
SQL>select table_name, column_name, endpoint_number, endpoint_value
from user_histograms
where table_name ='TEST_0211'
and column_name ='OBJECT_ID'
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
-------------- ---------------------------- --------------
TEST_0211 OBJECT_ID02
TEST_0211 OBJECT_ID1583
TEST_0211 OBJECT_ID21150
TEST_0211 OBJECT_ID31702
TEST_0211 OBJECT_ID42254
TEST_0211 OBJECT_ID52806
TEST_0211 OBJECT_ID63358
TEST_0211 OBJECT_ID73920
TEST_0211 OBJECT_ID84477
TEST_0211 OBJECT_ID95051
TEST_0211 OBJECT_ID105611
TEST_0211 OBJECT_ID116264
TEST_0211 OBJECT_ID126816
TEST_0211 OBJECT_ID137387
TEST_0211 OBJECT_ID147949
TEST_0211 OBJECT_ID158508
TEST_0211 OBJECT_ID169093
TEST_0211 OBJECT_ID179724
TEST_0211 OBJECT_ID1810438
通过运算,可以得知每个bucket大约会有551个成员 以下是由width_bucket函数构造的柱状图:
SQL>selectcount(*) from test_0211;
COUNT(*)
----------
9924
SQL>selectfloor(9924/18) from dual;
FLOOR(9924/18)
--------------
551
selectdistinct table_name,
column_name,
endpoint_number,
max(object_id) over(partition by endpoint_number) as endpoint_number
from (SELECT'TEST_0211' table_name,
'OBJECT_ID' column_name,
object_id,
WIDTH_BUCKET(object_id, 0, 9924, 18) ENDpoint_number
FROM test_0211) a
orderby3
TABLE_NAM COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_NUMBER
--------- ------------------ --------------- ---------------
TEST_0211 OBJECT_ID1551
TEST_0211 OBJECT_ID21102
TEST_0211 OBJECT_ID31653
TEST_0211 OBJECT_ID42205
TEST_0211 OBJECT_ID52756
TEST_0211 OBJECT_ID63307
TEST_0211 OBJECT_ID73859
TEST_0211 OBJECT_ID84410
TEST_0211 OBJECT_ID94961
TEST_0211 OBJECT_ID105513
TEST_0211 OBJECT_ID116064
TEST_0211 OBJECT_ID126615
TEST_0211 OBJECT_ID137167
TEST_0211 OBJECT_ID147718
TEST_0211 OBJECT_ID158269
TEST_0211 OBJECT_ID168821
TEST_0211 OBJECT_ID179372
TEST_0211 OBJECT_ID189923
TEST_0211 OBJECT_ID1910438
