oracle文档中说(文档 ID 338926.1)
Oracle determines the columns to collect histograms based on data distribution and the workload of the columns.
我测试了一下为何没有直方图，除非我手工指定size 的大小
SQL>select object_id from dba_objects where owner='NEWSADMIN' and object_name='CPI_PLACEAPPLY';
OBJECT_ID
----------
95800
SQL>select name,intcol# from sys.col$ where obj#=95800 and name='PLACEOBJECTCODE';
NAME
INTCOL#
-------------------------------------------------------------------------------- ----------
PLACEOBJECTCODE
3
SQL>select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=95800;
OBJ#INTCOL# EQUALITY_PREDS
---------- ---------- --------------
95800
1
223
95800
2
417
95800
3
414
95800 11
0
95800 16
111
95800 20
35
95800
4
152
95800
6
107
95800
7
122
95800
5
0
95800 12
107
95800 22
326
95800 23
233
95800 25
151
95800 24
150
15 rows selected
SQL>select count(distinct PLACEOBJECTCODE) from newsadmin.CPI_PLACEAPPLY;
COUNT(DISTINCTPLACEOBJECTCODE)
------------------------------
6082
SQL>select count(*) from newsadmin.CPI_PLACEAPPLY;
COUNT(*)
----------
403449
--auto 方式收集统计信息
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'newsadmin',
tabname => 'CPI_PLACEAPPLY',
estimate_percent => 100,
degree => 4,
method_opt => 'for all columns size auto',
cascade=>TRUE
);
END;
SQL> select a.owner ||'.'||a.table_name name ,a.column_name,b.num_rows,a.num_distinct Cardinality,a.num_distinct/b.num_rows selectivity,
2 num_nulls,density,a.histogram,a.num_buckets from dba_tab_col_statistics a,dba_tables b where a.owner=b.owner and a.table_name=b.table_name
3 and a.owner=upper('newsadmin') and a.table_name=upper('CPI_PLACEAPPLY') and column_name='PLACEOBJECTCODE' ;
NAME
COLUMN_NAME
NUM_ROWS CARDINALITY SELECTIVITYNUM_NULLSDENSITY HISTOGRAM NUM_BUCKETS
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ---------- ----------- ----------- ---------- ---------- --------------- -----------
NEWSADMIN.CPI_PLACEAPPLY
PLACEOBJECTCODE
4034246081 0.01507347171027 0.00016444 NONE
1 |
