【练习】ORACLE统计信息--直方图

时间:2022-04-24 02:03:04
①创建表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.