oracle還可以通直方圖來收集統計資訊，這個例子值收集一個表的直方圖統計資訊：
以下是實驗過程：

---建立測試表：
SQL> create table t as select * from dba_objects;
Table created.

---呼叫系統的 dbms_stats包收集統計資訊：
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL procedure successfully completed.

---此時資料庫為表上的所有欄位收集了預設的統計資訊，每個列兩個Bucket：
col column_name for a30
col owner for a10
col table_name for a10
col ENDPOINT_ACTUAL_VALUE for a10
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYSTEM' order by 3;
OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ------------------------------ --------------- -------------- ----------
SYSTEM T CREATED 1 2457674.88
SYSTEM T CREATED 0 2456529.48
SYSTEM T DATA_OBJECT_ID 1 89403
SYSTEM T DATA_OBJECT_ID 0 0
SYSTEM T GENERATED 0 4.0500E+35
SYSTEM T GENERATED 1 4.6211E+35
SYSTEM T LAST_DDL_TIME 1 2457674.88
SYSTEM T LAST_DDL_TIME 0 2452549.53
SYSTEM T NAMESPACE 0 1
SYSTEM T NAMESPACE 1 64
SYSTEM T OBJECT_ID 1 89403

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ------------------------------ --------------- -------------- ----------
SYSTEM T OBJECT_ID 0 2
SYSTEM T OBJECT_NAME 0 2.4504E+35
SYSTEM T OBJECT_NAME 1 6.2963E+35
SYSTEM T OBJECT_TYPE 0 3.4943E+35
SYSTEM T OBJECT_TYPE 1 4.5849E+35
SYSTEM T OWNER 0 3.3913E+35
SYSTEM T OWNER 1 4.5831E+35
SYSTEM T SECONDARY 1 4.6211E+35
SYSTEM T SECONDARY 0 4.0500E+35
SYSTEM T STATUS 1 4.4786E+35
SYSTEM T STATUS 0 4.4786E+35

OWNER TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ------------------------------ --------------- -------------- ----------
SYSTEM T SUBOBJECT_NAME 1 4.5340E+35
SYSTEM T SUBOBJECT_NAME 0 1.8867E+35
SYSTEM T TEMPORARY 1 4.6211E+35
SYSTEM T TEMPORARY 0 4.0500E+35
SYSTEM T TIMESTAMP 0 2.5558E+35
SYSTEM T TIMESTAMP 1 2.6059E+35
28 rows selected.
#共28條記錄。

---同時，列的低值、高值等資訊會被收集記錄在dba_tab_col_statistics中：
SQL> set linesize 150;
SQL> select table_name,column_name,num_distinct,low_value,high_value,DENSITY
from dba_tab_col_statistics where owner='SYSTEM' and table_name='T';

TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ------------ ------------------------------ ------------------------------ ----------
T OWNER 32 415045585F303330323030 584442 .03125
T OBJECT_NAM 52336 2F31303030333233645F44656C6567 794362437253756253616D706C696E .000019107
E 617465496E766F636174696F6E4861 67547970653232395F54

T SUBOBJECT_ 148 2456534E5F31 575248245F5741495453545F323338 .006756757
NAME 3739363238335F30

T OBJECT_ID 87106 C103 C3095F04 .00001148
T DATA_OBJEC 9218 80 C3095F04 .000108483
T_ID

TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ------------ ------------------------------ ------------------------------ ----------
T OBJECT_TYP 45 434C5553544552 584D4C20534348454D41 .022222222
E

T CREATED 949 787108180C2624 78740A0C160334 .001053741
T LAST_DDL_T 1057 78660A010D2A32 78740A0C160334 .000946074
IME

T TIMESTAMP 1094 313939302D30382D32363A31313A32 323031362D31302D31323A32313A30 .000914077
353A3030 323A3531

T STATUS 1 56414C4944 56414C4944 1

TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY
---------- ---------- ------------ ------------------------------ ------------------------------ ----------
T TEMPORARY 2 4E 59 .5
T GENERATED 2 4E 59 .5
T SECONDARY 2 4E 59 .5
T NAMESPACE 21 C102 C141 .047619048
T EDITION_NA 0 0
ME

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.

---刪除以上的兩個引數之後：
SQL> select * from dba_tab_histograms where table_name='T' and owner='SYSTEM' order by 3;

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T CREATED 1 2457674.88
SYSTEM T CREATED 0 2456529.48
SYSTEM T DATA_OBJEC 1 89403
T_ID

SYSTEM T DATA_OBJEC 0 0
T_ID

SYSTEM T GENERATED 0 4.0500E+35
SYSTEM T GENERATED 1 4.6211E+35
SYSTEM T LAST_DDL_T 1 2457674.88

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME

SYSTEM T LAST_DDL_T 0 2452549.53
IME

SYSTEM T NAMESPACE 0 1
SYSTEM T NAMESPACE 1 64
SYSTEM T OBJECT_ID 1 89403
SYSTEM T OBJECT_ID 0 2
SYSTEM T OBJECT_NAM 0 2.4504E+35
E

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------

SYSTEM T OBJECT_NAM 1 6.2963E+35
E

SYSTEM T OBJECT_TYP 0 3.4943E+35
E

SYSTEM T OBJECT_TYP 1 4.5849E+35
E

SYSTEM T OWNER 0 3.3913E+35

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T OWNER 1 4.5831E+35
SYSTEM T SECONDARY 1 4.6211E+35
SYSTEM T SECONDARY 0 4.0500E+35
SYSTEM T STATUS 1 4.4786E+35
SYSTEM T STATUS 0 4.4786E+35
SYSTEM T SUBOBJECT_ 1 4.5340E+35
NAME
#共28條記錄。

--- 透過dbms_stats.delete_column_stats包刪除owner欄位：
SQL> exec dbms_stats.delete_column_stats(user,'T','OWNER');
PL/SQL procedure successfully completed.

SQL> select * from dba_tab_histograms where table_name='T' and owner='SYSTEM' order by 3;

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T CREATED 1 2457674.88
SYSTEM T CREATED 0 2456529.48
SYSTEM T DATA_OBJEC 1 89403
T_ID

SYSTEM T DATA_OBJEC 0 0
T_ID

SYSTEM T GENERATED 0 4.0500E+35
SYSTEM T GENERATED 1 4.6211E+35
SYSTEM T LAST_DDL_T 1 2457674.88

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME

SYSTEM T LAST_DDL_T 0 2452549.53
IME

SYSTEM T NAMESPACE 1 64
SYSTEM T NAMESPACE 0 1
SYSTEM T OBJECT_ID 1 89403
SYSTEM T OBJECT_ID 0 2
SYSTEM T OBJECT_NAM 0 2.4504E+35
E

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------

SYSTEM T OBJECT_NAM 1 6.2963E+35
E

SYSTEM T OBJECT_TYP 1 4.5849E+35
E

SYSTEM T OBJECT_TYP 0 3.4943E+35
E

SYSTEM T SECONDARY 0 4.0500E+35

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T SECONDARY 1 4.6211E+35
SYSTEM T STATUS 0 4.4786E+35
SYSTEM T STATUS 1 4.4786E+35
SYSTEM T SUBOBJECT_ 0 1.8867E+35
NAME

SYSTEM T SUBOBJECT_ 1 4.5340E+35
NAME

SYSTEM T TEMPORARY 0 4.0500E+35
SYSTEM T TEMPORARY 1 4.6211E+35

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T TIMESTAMP 1 2.6059E+35
SYSTEM T TIMESTAMP 0 2.5558E+35

26 rows selected.

---透過執行包dbms_stats.gather_table_stats 再次收集，發現地段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='SYSTEM' order by 3;

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T CREATED 1 2457674.88
SYSTEM T CREATED 0 2456529.48
SYSTEM T DATA_OBJEC 1 89403
T_ID

SYSTEM T DATA_OBJEC 0 0
T_ID

SYSTEM T GENERATED 0 4.0500E+35
SYSTEM T GENERATED 1 4.6211E+35
SYSTEM T LAST_DDL_T 1 2457674.88

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
IME

SYSTEM T LAST_DDL_T 0 2452549.53
IME

SYSTEM T NAMESPACE 0 1
SYSTEM T NAMESPACE 1 64
SYSTEM T OBJECT_ID 1 89403
SYSTEM T OBJECT_ID 0 2
SYSTEM T OBJECT_NAM 0 2.4504E+35
E

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------

SYSTEM T OBJECT_NAM 1 6.2963E+35
E

SYSTEM T OBJECT_TYP 0 3.4943E+35
E

SYSTEM T OBJECT_TYP 1 4.5849E+35
E

SYSTEM T OWNER 0 3.3913E+35

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T OWNER 1 4.5831E+35
SYSTEM T SECONDARY 1 4.6211E+35
SYSTEM T SECONDARY 0 4.0500E+35
SYSTEM T STATUS 1 4.4786E+35
SYSTEM T STATUS 0 4.4786E+35
SYSTEM T SUBOBJECT_ 1 4.5340E+35
NAME

SYSTEM T SUBOBJECT_ 0 1.8867E+35
NAME

OWNER TABLE_NAME COLUMN_NAM ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_A
---------- ---------- ---------- --------------- -------------- ----------
SYSTEM T TEMPORARY 1 4.6211E+35
SYSTEM T TEMPORARY 0 4.0500E+35
SYSTEM T TIMESTAMP 0 2.5558E+35
SYSTEM T TIMESTAMP 1 2.6059E+35
28 rows selected.
#共28條記錄。

oracle統計資訊--直方圖的收集：

相關文章