首页 > 代码库 > 【练习】ORACLE统计信息--直方图

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

2024-08-16 10:31:31   225人阅读

①创建表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 a30SQL> col owner for a10SQL> col table_name for a10SQL> 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 12457673.05SYS T CREATED 02456529.48SYS T DATA_OBJECT_ID 188710OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T DATA_OBJECT_ID 00SYS T GENERATED 04.0500E+35SYS T GENERATED 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T LAST_DDL_TIME 12457673.05SYS T LAST_DDL_TIME 02452549.53SYS T NAMESPACE 01OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T NAMESPACE 164SYS T OBJECT_ID 188710SYS T OBJECT_ID 02OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_NAME 02.4504E+35SYS T OBJECT_NAME 16.2963E+35SYS T OBJECT_TYPE 03.4943E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_TYPE 14.5849E+35SYS T OWNER 03.3913E+35SYS T OWNER 14.5831E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T SECONDARY 14.6211E+35SYS T SECONDARY 04.0500E+35SYS T STATUS 14.4786E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T STATUS 04.4786E+35SYS T SUBOBJECT_NAME 14.5340E+35SYS T SUBOBJECT_NAME 01.8867E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TEMPORARY 14.6211E+35SYS T TEMPORARY 04.0500E+35SYS T TIMESTAMP 02.5558E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TIMESTAMP 12.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 29415045585F303330323030584442 .034482759T OBJECT_NAME 522122F31303030333233645F44656C6567617465496E766F636174696F6E4861794362437253756253616D706C696E67547970653232395F54 .000019153TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------T SUBOBJECT_NAME 1502456534E5F31575248245F5741495453545F3234313436323135355F30 .006666667T OBJECT_ID 86995C103TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------C309580B .000011495T DATA_OBJECT_ID 910680C309580B .000109818T OBJECT_TYPE 45TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------434C5553544552584D4C20534348454D41 .022222222T CREATED 978787108180C262478740A0B02082A .001022495TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------T LAST_DDL_TIME 109678660A010D2A3278740A0B02082A .000912409T TIMESTAMP 1130313939302D30382D32363A31313A32353A3030323031362D31302D31313A30313A30373A3431 .000884956TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------T STATUS 156414C494456414C4944 1T TEMPORARY 24ETABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------59 .5T GENERATED 24E59 .5T SECONDARY 2TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------4E59 .5T NAMESPACE 21C102C141 .047619048TABLE_NAME COLUMN_NAME NUM_DISTINCT---------- ------------------------------ ------------LOW_VALUE----------------------------------------------------------------HIGH_VALUE DENSITY---------------------------------------------------------------- ----------T EDITION_NAME 0015 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 12457673.05SYS T CREATED 02456529.48SYS T DATA_OBJECT_ID 188710OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T DATA_OBJECT_ID 00SYS T GENERATED 04.0500E+35SYS T GENERATED 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T LAST_DDL_TIME 12457673.05SYS T LAST_DDL_TIME 02452549.53SYS T NAMESPACE 01OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T NAMESPACE 164SYS T OBJECT_ID 188710SYS T OBJECT_ID 02OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_NAME 02.4504E+35SYS T OBJECT_NAME 16.2963E+35SYS T OBJECT_TYPE 03.4943E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_TYPE 14.5849E+35SYS T OWNER 03.3913E+35SYS T OWNER 14.5831E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T SECONDARY 14.6211E+35SYS T SECONDARY 04.0500E+35SYS T STATUS 14.4786E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T STATUS 04.4786E+35SYS T SUBOBJECT_NAME 14.5340E+35SYS T SUBOBJECT_NAME 01.8867E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TEMPORARY 14.6211E+35SYS T TEMPORARY 04.0500E+35SYS T TIMESTAMP 02.5558E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TIMESTAMP 12.6059E+3528 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 12457673.05SYS T CREATED 02456529.48SYS T DATA_OBJECT_ID 188710OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T DATA_OBJECT_ID 00SYS T GENERATED 04.0500E+35SYS T GENERATED 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T LAST_DDL_TIME 12457673.05SYS T LAST_DDL_TIME 02452549.53SYS T NAMESPACE 164OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T NAMESPACE 01SYS T OBJECT_ID 188710SYS T OBJECT_ID 02OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_NAME 02.4504E+35SYS T OBJECT_NAME 16.2963E+35SYS T OBJECT_TYPE 14.5849E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_TYPE 03.4943E+35SYS T SECONDARY 04.0500E+35SYS T SECONDARY 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T STATUS 04.4786E+35SYS T STATUS 14.4786E+35SYS T SUBOBJECT_NAME 01.8867E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T SUBOBJECT_NAME 14.5340E+35SYS T TEMPORARY 04.0500E+35SYS T TEMPORARY 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TIMESTAMP 12.6059E+35SYS T TIMESTAMP 02.5558E+3526 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 12457673.05SYS T CREATED 02456529.48SYS T DATA_OBJECT_ID 188710OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T DATA_OBJECT_ID 00SYS T GENERATED 04.0500E+35SYS T GENERATED 14.6211E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T LAST_DDL_TIME 12457673.05SYS T LAST_DDL_TIME 02452549.53SYS T NAMESPACE 01OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T NAMESPACE 164SYS T OBJECT_ID 188710SYS T OBJECT_ID 02OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_NAME 02.4504E+35SYS T OBJECT_NAME 16.2963E+35SYS T OBJECT_TYPE 03.4943E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T OBJECT_TYPE 14.5849E+35SYS T OWNER 03.3913E+35SYS T OWNER 14.5831E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T SECONDARY 14.6211E+35SYS T SECONDARY 04.0500E+35SYS T STATUS 14.4786E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T STATUS 04.4786E+35SYS T SUBOBJECT_NAME 14.5340E+35SYS T SUBOBJECT_NAME 01.8867E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TEMPORARY 14.6211E+35SYS T TEMPORARY 04.0500E+35SYS T TIMESTAMP 02.5558E+35OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER---------- ---------- ------------------------------ ---------------ENDPOINT_VALUE ENDPOINT_A-------------- ----------SYS T TIMESTAMP 12.6059E+3528 rows selected.SQL> exec dbms_stats.gather_table_stats(user,‘T‘,method_opt=>‘for columns owner size 1‘);PL/SQL procedure successfully completed.

 

 

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


联系
我们