①创建表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
28 rows selected.
--同时，列的低值、高值等信息会被收集记录在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.

最新文章

  1. Adding a WebPart to a SharePoint 2013 Master Page 分类： Sharepoint 2015-07-08 01:03 7人阅读 评论(0) 收藏
  2. iOS10 权限适配
  3. HDU 4949 Light（插头dp、位运算）
  4. 在Spring中使用脚本
  5. php不使用插件导出excel
  6. 详解 $_SERVER 函数中QUERY_STRING和REQUEST_URI区别
  7. MySQL（20）：事务简介 和 事务的四个特性
  8. PostgreSQL相关的软件，库，工具和资源集合
  9. 百度地图实现车辆轨迹移动播放(baidu map api)
  10. WWDC心愿单:新版OS X或将有这些变化
  11. BZOJ-1007-水平可见直线-HN2008
  12. TotoiseSVN基本用法
  13. Android - 支持不同的设备 - 支持不同的屏幕
  14. (原创)用JAX-WS+Spring实现简单soap规范的webservice
  15. ios设置音乐audio自动播放
  16. Oracle的decode、sign、trunc函数
  17. docker内程序如何读取dockerfile和compose.yml中设置的环境变量
  18. H - An Easy Problem?!
  19. Qt画笔实现曲线
  20. Tomcat 动态数据库连接池

热门文章

  1. C# DEBUG 调试信息打印及输出详解
  2. 黄聪：Discuz!X3.2 如何配置超级版主或者某些管理员，允许管理用户组或者权限
  3. opencv，关于物体检测
  4. Win7+VMware Workstation环境下的CentOS-Linux网络连接设置
  5. HTML5与HTML4的区别
  6. JAVA 文档注释，类的说明，HTML说明文档的生成
  7. Jquery Validation 插件验证手机号
  8. python数据分析师面试题选
  9. Java高级开发工程师
  10. C++学习18 派生类的析构函数