[20180122]列統計與直方圖.txt

[20180122]列統計與直方圖.txt

--//昨天看了提到分析method的各種寫法,自己重複驗證看看:

1.環境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t1 as select object_type o1, object_type o2, object_type o3, object_id, object_name from all_objects where rownum <= 50000;

SCOTT@book> select  column_name, num_distinct, histogram, num_buckets, last_analyzed from  user_tab_cols where table_name = 'T1' order by column_id;
COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NUM_BUCKETS LAST_ANALYZED
-------------------- ------------ --------------- ----------- -------------------
O1                                NONE
O2                                NONE
O3                                NONE
OBJECT_ID                         NONE
OBJECT_NAME                       NONE

--//建立後沒有分析.

2.測試:

exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1 for columns o1 o2 o3 size 15');

SCOTT@book> select  column_name, num_distinct, histogram, num_buckets, last_analyzed,histogram from  user_tab_cols where table_name = 'T1' order by column_id;
COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       HISTOGRAM
-------------------- ------------ --------------- ----------- ------------------- ---------------
O1                             33 NONE                      1 2018-01-22 08:52:44 NONE
O2                             33 NONE                      1 2018-01-22 08:52:44 NONE
O3                             33 HEIGHT BALANCED          15 2018-01-22 08:52:44 HEIGHT BALANCED
OBJECT_ID                   50000 NONE                      1 2018-01-22 08:52:44 NONE
OBJECT_NAME                 28576 NONE                      1 2018-01-22 08:52:44 NONE
--//注意看O1,O2欄位沒有建立直方圖.

SCOTT@book> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1 for columns size 15 o1 o2 o3');
PL/SQL procedure successfully completed.

SCOTT@book> select  column_name, num_distinct, histogram, num_buckets, last_analyzed,histogram from  user_tab_cols where table_name = 'T1' order by column_id;
COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       HISTOGRAM
-------------------- ------------ --------------- ----------- ------------------- ---------------
O1                             33 HEIGHT BALANCED          15 2018-01-22 08:57:37 HEIGHT BALANCED
O2                             33 HEIGHT BALANCED          15 2018-01-22 08:57:37 HEIGHT BALANCED
O3                             33 HEIGHT BALANCED          15 2018-01-22 08:57:37 HEIGHT BALANCED
OBJECT_ID                   50000 NONE                      1 2018-01-22 08:57:37 NONE
OBJECT_NAME                 28576 NONE                      1 2018-01-22 08:57:37 NONE
--//這樣O1,O2,O3欄位建立直方圖.

--//我個人喜歡的寫法是:
SCOTT@book> exec dbms_stats.gather_table_stats(user,'t1',method_opt=>'for all columns size 1 for columns o1 size 15 o2 size 16 o3 size 17');
PL/SQL procedure successfully completed.

SCOTT@book> select  column_name, num_distinct, histogram, num_buckets, last_analyzed,histogram from  user_tab_cols where table_name = 'T1' order by column_id;
COLUMN_NAME          NUM_DISTINCT HISTOGRAM       NUM_BUCKETS LAST_ANALYZED       HISTOGRAM
-------------------- ------------ --------------- ----------- ------------------- ---------------
O1                             33 HEIGHT BALANCED          15 2018-01-22 08:58:46 HEIGHT BALANCED
O2                             33 HEIGHT BALANCED          16 2018-01-22 08:58:46 HEIGHT BALANCED
O3                             33 HEIGHT BALANCED          17 2018-01-22 08:58:46 HEIGHT BALANCED
OBJECT_ID                   50000 NONE                      1 2018-01-22 08:58:46 NONE
OBJECT_NAME                 28576 NONE                      1 2018-01-22 08:58:46 NONE
--//這樣O1,O2,O3欄位建立直方圖.作者提倡的寫法: "for columns {size clause} {column list}".這樣反過來我個人不習慣.^_^.

--//作者還給出一些例子,大家執行測試:
for columns o1 size 12, o2 size 13, o3 size 14
for columns o1 size 15 o2 size 16 o3 size 17
for columns size 18 o1 size 19 o2 size 20 o3
for columns size 21 o1 o2 size 22 o3
for columns o1 size 12, o2 size 12, o3 size 13, object_id size 13 object_name size 14
for columns size 22 o1 o2 for columns size 23 o3 object_id for columns size 24  object_name

--//總之注意這樣寫法'for all columns size 1 for columns size 15 o1 o2 o3'可能不滿足需要.在工作中要引起足夠的注意.