oracle 11g在統計資訊收集方面增加了擴充套件統計資訊的特性,它可以收集一個表中相關列上的統計資訊,也可以收集函式表示式上的 統計資訊.使選擇率,成本的估計更加準確,也更容易走正確的執行計劃.在相關列上收集統計資訊,好處還是很明顯的.例如兩列在邏輯 上有一定的關係,但如果只是對這兩個列單獨做統計資訊的收集,根據多條件的選擇率計算{(A AND B的選擇率為:OPSEL[a]*OPSEL[b]); (A OR B 的選擇率為:OPSEL[a]+OPSEL[b]-OPSEL[a]OPSEL[b]);(NOT A的選擇率為:1-OPSEL[a])},估算出來的選擇率就可能偏差很大. 以下測試: DB Version:11.2.0.4 ----產生測試資料 drop table scott.test01 purge; create table scott.test01 as select * from dba_objects; --把object_name 更新為和object_type一樣,用於測試. update scott.test01 set object_name=object_type; commit; 1.收集單列統計資訊,檢視執行計劃 --收集單列統計資訊 begin dbms_stats.gather_table_stats('scott','test01'); end; --檢視錶的行數 select  table_name,num_rows from dba_tables where owner = 'SCOTT' and table_name = 'TEST01'; /* TABLE_NAME  NUM_ROWS TEST01  87212 */ --產生語句的執行計劃 explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'; SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,        options,        object_name,        cardinality,        bytes,        io_cost,        cpu_cost,        cost,        time   FROM plan_table  START WITH id = 0 CONNECT BY PRIOR id = parent_id; /* OPERATION  OPTIONS  OBJECT_NAME  CARDINALITY  BYTES  IO_COST  CPU_COST  COST  TIME SELECT STATEMENT      41  3362  347  35338490  348  5   TABLE ACCESS  FULL  TEST01  41  3362  347  35338490  348  5 */ 這裡可以看到,估算的返回行數是41,顯然和實際相差很遠 rollback; --行數估算   select rpad(column_name, 30, ' ') column_name,        rpad(num_distinct, 8, ' ') num_distinct,        rpad(utl_raw.cast_to_varchar2(low_value), 15, ' ') low_value,        rpad(utl_raw.cast_to_varchar2(high_value), 10, ' ') high_value,        rpad(num_nulls, 8, ' ') num_nulls,        rpad(avg_col_len, 6, ' ') avg_col_len,        rpad(density, 20, ' ') density,        histogram   from dba_tab_col_statistics  where owner = 'SCOTT'    and table_name = 'TEST01'    and column_name  in ('OBJECT_NAME','OBJECT_TYPE'); /*    COLUMN_NAME  NUM_DISTINCT  LOW_VALUE  HIGH_VALUE  NULLABLE  NUM_NULLS  AVG_COL_LEN  DENSITY  HISTOGRAM OBJECT_NAME   46        CLUSTER          XML SCHEMA  Y         0         9       .0217391304347826     NONE OBJECT_TYPE   46        CLUSTER          XML SCHEMA  Y         0         9       .0217391304347826     NONE */ 估算的返回行數是41,是由兩個列的density相乘再乘以表的行數得到,.0217391304347826*.0217391304347826*87212=41.2155009451796=41     2.收集多列擴充套件統計資訊,檢視執行計劃 --收集多列擴充套件統計資訊   begin   dbms_stats.gather_table_stats('scott','test01',method_opt =>'for columns (object_name,object_type)');   end; --產生語句的執行計劃     explain plan for select * from scott.test01 where object_name='INDEX' and object_type='INDEX'; SELECT lpad(' ', 2 * (LEVEL - 1)) || operation operation,        options,        object_name,        cardinality,        bytes,        io_cost,        cpu_cost,        cost,        time   FROM plan_table  START WITH id = 0 CONNECT BY PRIOR id = parent_id; /* OPERATION  OPTIONS  OBJECT_NAME  CARDINALITY  BYTES  IO_COST  CPU_COST  COST  TIME SELECT STATEMENT            5303    498482    347    36285951    348    5   TABLE ACCESS    FULL    TEST01    5303    498482    347    36285951    348    5 */ 這裡可以看到,估算的返回行數是5303,已經基本上和實際返回行數相近. PS: 1.擴充套件統計資訊的收集,可以用select dbms_stats.create_extended_stats('scott','test01','(object_name,object_type)')from dual 建立擴充套件統計列,然後dbms_stats.gather_table_stats('scott','test01')收集統計資訊,也可以直接在 dbms_stats.gather_table_stats中的method_opt屬性同時建立擴充套件統計又收集統計資料. 2.oracle 11g不僅可以收集多列擴充套件統計資訊,還可以收集函式和表示式的擴充套件統計資訊.

oracle 11g 擴充套件統計資訊extended_stats

相關文章