Oracle Column Group Statistics

基數是CBO透過行資源或行資源組合評估出來的操作將產生的行數。在有些情況下，結果集的基數可能被評估錯誤。最常見的是使用複雜謂詞且統計資訊不能精確反映出謂詞相關聯的資料。例如:select ename from emp where sal >= 25000 and job = 'PRESIDENT';
在這個例子中，在sal與job列中的資料存在一種隱藏的關聯。只有董事長才會掙的比$25,000多。沒有其它的員工滿足sal>=25000。最佳化器沒有辦法檢查這種關聯且會將這兩個列單獨對待。這將會對謂詞產生一種不理解的選擇率以及不精確的基數評估。

在Oracle 11g及以上版本透過對一組列建立擴充套件統計資訊來解決這種問題。

SQL> create table t1(t_name varchar2(20) not null,t_country varchar2(20) not null,t_town varchar2(20) not null);

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T_NAME                                    NOT NULL VARCHAR2(20)
 T_COUNTRY                                 NOT NULL VARCHAR2(20)
 T_TOWN                                    NOT NULL VARCHAR2(20)


SQL> begin
  2   for i in 1..50 loop
  3       insert into t1 values('JY','CHINA','QIHE');  
  4   end loop;
  5   for r in 51 .. 400 loop
  6     insert into t1 values('A'||r,'USA','NewYork');  
  7   end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

收集表t1的統計資訊

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'T1',estimate_percent=>100,method_opt=>'for all columns sizeauto',cascade=>true);

PL/SQL procedure successfully completed.

查詢t_name='JY'且t_country='CHINA'的記錄為50

SQL> select count(*) from t1 where t_name='JY' and t_country='CHINA';

  COUNT(*)
----------
        50

執行查詢發現最佳化器評估的基數為7,與50相差很遠

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   119 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     7 |   119 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

建立組列

SQL> declare
  2  cg_name varchar2(30);
  3  begin
  4   cg_name :=  sys.dbms_stats.create_extended_stats(null,'t1', '(t_name,t_country)');
  5  end;
  6  /  

PL/SQL procedure successfully completed.

對建立的組列收集統計資訊

SQL> exec dbms_stats.gather_table_stats(null,'t1',estimate_percent=>100,method_opt=>'for columns(t_name,t_country) size skewonly');

PL/SQL procedure successfully completed.


SQL> SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='T1';


EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUVJ3HB84$ZIMSQJE_3AMWZ_9 ("T_NAME","T_COUNTRY")


SQL> SELECT e.extension col_group, t.num_distinct, t.histogram
  2  FROM user_stat_extensions e, user_tab_col_statistics t
  3  WHERE e.extension_name=t.column_name
  4  AND e.table_name = t.table_name
  5  AND t.table_name='T1';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------- ------------ ---------------
("T_NAME","T_COUNTRY")                                                                    351 HEIGHT BALANCED

建立組列收集擴充套件統計資訊後，其評估基數為47與實際的50相差很小，對於估算已經是很準確了

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    47 |   846 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    47 |   846 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T_COUNTRY"='CHINA' AND "T_NAME"='JY')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1224  bytes sent via SQL*Net to client
        452  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

對於列之間存在關聯關係的列建立Column Group Statistics可以讓最佳化器進行更精確的基數評估從而選擇最優執行計劃來提高效能