①创建表t
SQL> create table t as select * from dba_objects;
Table created.
--收集直方图
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
--此时数据库为表上的所有字段收集了缺省的统计信息，每个列两个Bucket：
SQL> col column_name for a30
SQL> col owner for a10
SQL> col table_name for a10
SQL> col ENDPOINT_ACTUAL_VALUE for a10
②查询
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T CREATED 1
2457673.05
SYS T CREATED 0
2456529.48
SYS T DATA_OBJECT_ID 1
88710
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T DATA_OBJECT_ID 0
0
SYS T GENERATED 0
4.0500E+35
SYS T GENERATED 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T LAST_DDL_TIME 1
2457673.05
SYS T LAST_DDL_TIME 0
2452549.53
SYS T NAMESPACE 0
1
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T NAMESPACE 1
64
SYS T OBJECT_ID 1
88710
SYS T OBJECT_ID 0
2
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_NAME 0
2.4504E+35
SYS T OBJECT_NAME 1
6.2963E+35
SYS T OBJECT_TYPE 0
3.4943E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_TYPE 1
4.5849E+35
SYS T OWNER 0
3.3913E+35
SYS T OWNER 1
4.5831E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T SECONDARY 1
4.6211E+35
SYS T SECONDARY 0
4.0500E+35
SYS T STATUS 1
4.4786E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T STATUS 0
4.4786E+35
SYS T SUBOBJECT_NAME 1
4.5340E+35
SYS T SUBOBJECT_NAME 0
1.8867E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TEMPORARY 1
4.6211E+35
SYS T TEMPORARY 0
4.0500E+35
SYS T TIMESTAMP 0
2.5558E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TIMESTAMP 1
2.6059E+35
--同时，列的低值、高值等信息会被收集记录在dba_tab_col_statistics中：
SQL> select table_name,column_name,num_distinct,low_value,high_value,DENSITY from dba_tab_col_statistics where owner='SYS' and table_name='T';
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
T OWNER 29
415045585F303330323030
584442 .034482759
T OBJECT_NAME 52212
2F31303030333233645F44656C6567617465496E766F636174696F6E4861
794362437253756253616D706C696E67547970653232395F54 .000019153
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
T SUBOBJECT_NAME 150
2456534E5F31
575248245F5741495453545F3234313436323135355F30 .006666667
T OBJECT_ID 86995
C103
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
C309580B .000011495
T DATA_OBJECT_ID 9106
80
C309580B .000109818
T OBJECT_TYPE 45
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
434C5553544552
584D4C20534348454D41 .022222222
T CREATED 978
787108180C2624
78740A0B02082A .001022495
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
T LAST_DDL_TIME 1096
78660A010D2A32
78740A0B02082A .000912409
T TIMESTAMP 1130
313939302D30382D32363A31313A32353A3030
323031362D31302D31313A30313A30373A3431 .000884956
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
T STATUS 1
56414C4944
56414C4944 1
T TEMPORARY 2
4E
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
59 .5
T GENERATED 2
4E
59 .5
T SECONDARY 2
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
4E
59 .5
T NAMESPACE 21
C102
C141 .047619048
TABLE_NAME COLUMN_NAME NUM_DISTINCT
---------- ------------------------------ ------------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE DENSITY
---------------------------------------------------------------- ----------
T EDITION_NAME 0
0
15 rows selected.
③SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=> 'for all columns size 1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns owner size 1');
PL/SQL procedure successfully completed.
或删除owner字段统计信息，（无用）
--查询发现直方图信息并未被删除
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T CREATED 1
2457673.05
SYS T CREATED 0
2456529.48
SYS T DATA_OBJECT_ID 1
88710
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T DATA_OBJECT_ID 0
0
SYS T GENERATED 0
4.0500E+35
SYS T GENERATED 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T LAST_DDL_TIME 1
2457673.05
SYS T LAST_DDL_TIME 0
2452549.53
SYS T NAMESPACE 0
1
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T NAMESPACE 1
64
SYS T OBJECT_ID 1
88710
SYS T OBJECT_ID 0
2
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_NAME 0
2.4504E+35
SYS T OBJECT_NAME 1
6.2963E+35
SYS T OBJECT_TYPE 0
3.4943E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_TYPE 1
4.5849E+35
SYS T OWNER 0
3.3913E+35
SYS T OWNER 1
4.5831E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T SECONDARY 1
4.6211E+35
SYS T SECONDARY 0
4.0500E+35
SYS T STATUS 1
4.4786E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T STATUS 0
4.4786E+35
SYS T SUBOBJECT_NAME 1
4.5340E+35
SYS T SUBOBJECT_NAME 0
1.8867E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TEMPORARY 1
4.6211E+35
SYS T TEMPORARY 0
4.0500E+35
SYS T TIMESTAMP 0
2.5558E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TIMESTAMP 1
2.6059E+35
28 rows selected.
④使用delete_column_stats可以彻底删除列的柱状图信息：
SQL> exec dbms_stats.delete_column_stats(user,'T','OWNER');
PL/SQL procedure successfully completed.
--此时查询发现owner字段在直方图中已经删除
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T CREATED 1
2457673.05
SYS T CREATED 0
2456529.48
SYS T DATA_OBJECT_ID 1
88710
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T DATA_OBJECT_ID 0
0
SYS T GENERATED 0
4.0500E+35
SYS T GENERATED 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T LAST_DDL_TIME 1
2457673.05
SYS T LAST_DDL_TIME 0
2452549.53
SYS T NAMESPACE 1
64
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T NAMESPACE 0
1
SYS T OBJECT_ID 1
88710
SYS T OBJECT_ID 0
2
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_NAME 0
2.4504E+35
SYS T OBJECT_NAME 1
6.2963E+35
SYS T OBJECT_TYPE 1
4.5849E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_TYPE 0
3.4943E+35
SYS T SECONDARY 0
4.0500E+35
SYS T SECONDARY 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T STATUS 0
4.4786E+35
SYS T STATUS 1
4.4786E+35
SYS T SUBOBJECT_NAME 0
1.8867E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T SUBOBJECT_NAME 1
4.5340E+35
SYS T TEMPORARY 0
4.0500E+35
SYS T TEMPORARY 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TIMESTAMP 1
2.6059E+35
SYS T TIMESTAMP 0
2.5558E+35
26 rows selected.
⑤再次收集，owner字段又回来了
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYS' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T CREATED 1
2457673.05
SYS T CREATED 0
2456529.48
SYS T DATA_OBJECT_ID 1
88710
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T DATA_OBJECT_ID 0
0
SYS T GENERATED 0
4.0500E+35
SYS T GENERATED 1
4.6211E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T LAST_DDL_TIME 1
2457673.05
SYS T LAST_DDL_TIME 0
2452549.53
SYS T NAMESPACE 0
1
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T NAMESPACE 1
64
SYS T OBJECT_ID 1
88710
SYS T OBJECT_ID 0
2
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_NAME 0
2.4504E+35
SYS T OBJECT_NAME 1
6.2963E+35
SYS T OBJECT_TYPE 0
3.4943E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T OBJECT_TYPE 1
4.5849E+35
SYS T OWNER 0
3.3913E+35
SYS T OWNER 1
4.5831E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T SECONDARY 1
4.6211E+35
SYS T SECONDARY 0
4.0500E+35
SYS T STATUS 1
4.4786E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T STATUS 0
4.4786E+35
SYS T SUBOBJECT_NAME 1
4.5340E+35
SYS T SUBOBJECT_NAME 0
1.8867E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TEMPORARY 1
4.6211E+35
SYS T TEMPORARY 0
4.0500E+35
SYS T TIMESTAMP 0
2.5558E+35
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER
---------- ---------- ------------------------------ ---------------
ENDPOINT_VALUE ENDPOINT_A
-------------- ----------
SYS T TIMESTAMP 1
2.6059E+35
28 rows selected.
SQL> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for columns owner size 1');
PL/SQL procedure successfully completed.