列の統計情報の収集と削除について

本論文はdbm_のみの使用に関するものである。列の統計情報を収集または削除するためのいくつかのコマンドがあります。
テスト表は以下の通りです。

SQL> create table test(i int,a varchar2(30));
Table created.

SQL> insert into test select rownum,object_name from all_objects;
9907 rows created.

簡単に言えば、列の統計情報は主に二つのタイプが含まれています。

基本情報しかありません。収集された統計情報は1つの桶（bucket）

だけです。

は、ヒストグラム情報を含む。収集された統計情報は、2〜254個の桶

を含む。
つまり、列の基本情報を収集したいと同時にヒストグラムを収集したくないという場合は、ブロックのsizeを指定する必要があります。

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 1');

PL/SQL procedure successfully completed.                                                                             

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,
avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN_NAM DISTINCT BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
---------- -------- ------- ---------- ---------- ---------- ----------- ----------
I              9907       1 C102       C26408     .000100939           4 NONE
A                                                                        NONE

列のヒストグラム情報を収集する場合、bucketの個数は2以上でなければならない（最大254を超えない）

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 2');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,
avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
------ -------- ------- --------- ---------- ---------- ----------- ---------------
I          9907       2 C102      C26408     .000100939           4 HEIGHT BALANCED
A                                                                   NONE

列の既存の棒グラフ情報を削除して列の基本統計情報を保持するには、改めてbucket 1の統計情報を収集する必要がある。

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt=>'for columns i size 1');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,
avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN DISTINCT BUCKETS LOW_VALUE  HIGH_VALUE    DENSITY AVG_COL_LEN HISTOGRAM
------ -------- ------- ---------- ---------- ---------- ----------- ----------
I          9907       1 C102       C26408     .000100939           4 NONE
A                                                                    NONE

この操作は明らかに不合理で、統計情報を再収集する際の代償が大きいので、Oracle 11 gはこれを改善し、ヒストグラムだけを削除して基本的な統計情報を保持することができます。命令文法は以下の通りです。

exec dbms_stats.delete_column_stats(user, 'TEST','I',col_stat_type=>'HISTOGRAM');

列全体の統計情報を完全に削除するには、delete_を呼び出す必要があります。コロムン.スタープロセス

SQL> exec dbms_stats.delete_column_stats(user, 'TEST', 'I');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,
avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN DISTINCT BUCKETS LOW_VALUE  HIGH_VALUE DENSITY  AVG_COL_LEN HISTOGRAM
------ -------- ------- ---------- ---------- -------  ----------- ----------
I                                                                  NONE
A                                                                  NONE

複数の列の統計情報は同じプロセスで収集され、異なる列のために異なるブロック数を指定することができる。

SQL> exec dbms_stats.gather_table_stats(user, 'TEST',
cascade=>false, method_opt => 'for columns size 1 T for columns size 2 A');

PL/SQL procedure successfully completed.

SQL> select column_name,num_distinct,num_buckets,low_value,high_value,density,
avg_col_len,histogram
  2  from user_tab_columns where table_name='TEST';

COLUMN DISTINCT BUCKETS LOW_VALUE HIGH_VALUE              DENSITY AVG_COL_LEN HISTOGRAM
------ -------- ------- --------- -------------------- ---------- ----------- ---------------
I          9907       1 C102      C26408               .000100939           4 NONE
A          7376       2 41        5F75746C245F6C6E635F .000185239          18 HEIGHT BALANCED
                                  696E645F7061727473

注目すべきは、9 iのdbms_statsの中で、method_optのデフォルト値はFOR ALL COLUMNS SIZE 1であり、つまり収集列の基本統計情報であり、ヒストグラム情報を収集しないが、10 gのデフォルト値はFOR ALL COLUCMNS SIZE AUTOとなり、Oracleは収集列の基本情報以外に、状況に応じていくつかの列の柱状図を収集する。