Oracle 11g的多列统计（Multi Column）值（下）-CSDN博客

上篇中，我们介绍了Multi-Column/Column Group的使用。本篇中我们继续研究其中的本质问题。

4、Multi-Column本质论

在Oracle中，是怎么组织多列组的信息呢？我们首先从列统计量入手分析。

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM from user_tab_col_statistics where table_name='T';

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

---------- ------------ ----------- ----------- ---------------

ID 3 5 3 FREQUENCY

NAME 4 5 3 FREQUENCY

SYS_STUIA0 4 5 12 FREQUENCY

V924QODN5R

5SCAKM60G#

在列中存在一个特殊列的统计信息，这里的列名同我们生成拓展统计量时候的那个字符串。

SQL> var vc_res varchar2(100);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','T','(id,name)');

PL/SQL procedure successfully completed

vc_res

---------

SYS_STUIA0V924QODN5R5SCAKM60G#

同时，Oracle也提供了一个视图user_stat_extensions来查看生成的拓展统计量。

SQL> select * from user_stat_extensions where extension_name = 'SYS_STUIA0V924QODN5R5SCAKM60G#';

TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPABLE

---------- ------------------------------ -------------------- ------- ---------

T SYS_STUIA0V924QODN5R5SCAKM60G# ("ID","NAME") USER YES

那么，Oracle是不是同函数索引采用相同的内部策略，构建一个虚拟列进行管理呢？我们只有去到col$基表中进行检查。

SQL> select object_id from dba_objects where object_name='T' and wner='SCOTT';

OBJECT_ID

----------

75482

SQL> select col#, name,DEFAULT$ from col$ where obj#=75482;

COL# NAME DEFAULT$

---------- ---------- ------------------------------

1 ID

2 NAME

0 SYS_STUIA0 SYS_OP_COMBINED_HASH("ID","NAM

V924QODN5R E")

5SCAKM60G#

果然，此处显示的内容是：Oracle使用类似虚拟列的方法，构建了一个列。之后对这个列进行统计量收集。

5、Column Group的失效场景

在笔者的实验中，发现并不是建立了column group之后，所有的统计量估算都是正确的。起码当条件中存在非等号之后，拓展统计量估值是可能错误的。

SQL> exec dbms_stats.gather_table_stats(user,'EMP',cascade => true);

PL/SQL procedure successfully completed

--结果集合为3

SQL> select * from emp where job='MANAGER' and sal>2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7566 JONES MANAGER 7839 1981-4-2 2975.00 20

7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30

7782 CLARK MANAGER 7839 1981-6-9 2450.00 10

查看执行计划中的估算值。

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("JOB"='MANAGER' AND "SAL">2000)

13 rows selected

此时，我们收集一下拓展统计量。

SQL> var vc_res varchar2(1000);

SQL> exec :vc_res := dbms_stats.create_extended_stats('SCOTT','EMP','(job,sal)');

PL/SQL procedure successfully completed

vc_res

---------

SYS_STU73TUM4UV1A$7U9OVY05$MH6

SQL> select * from user_stat_extensions;

TABLE_NAME EXTENSION_NAME EXTENSION CREATOR DROPPABLE

---------- ------------------------------ -------------------- ------- ---------

EMP SYS_STU73TUM4UV1A$7U9OVY05$MH6 ("JOB","SAL") USER YES

SQL> exec dbms_stats.gather_table_stats(user,'EMP',method_opt => 'for columns (job,sal) size skewonly');

PL/SQL procedure successfully completed

SQL> select column_name, num_distinct,SAMPLE_SIZE, AVG_COL_LEN, HISTOGRAM from user_tab_col_statistics where table_name='EMP';

COLUMN_NAM NUM_DISTINCT SAMPLE_SIZE AVG_COL_LEN HISTOGRAM

---------- ------------ ----------- ----------- ---------------

（篇幅原因，有省略。。。。。。）

DEPTNO 3 14 3 FREQUENCY

SYS_STU73T 12 14 12 FREQUENCY

UM4UV1A$7U

9OVY05$MH6

此时，执行计划并没有改变。

SQL> explain plan for select * from emp where job='MANAGER' and sal>2000;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3956160932

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 |

|* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 |

--------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter("JOB"='MANAGER' AND "SAL">2000)

13 rows selected

这说明拓展统计量在非等号情况下，是存在一些问题的。

6、其他注意问题和结论

此外，在使用column group上，我们还需要注意下面的问题：

ü 拓展列中不允许出现虚拟列（Virtual Column）；

ü 不能对sys schema下的数据表列建立column group；

ü 不能对聚簇表（Cluster Table）、索引组织表（Index Organized Table）、临时表（Temporary Table）和外部表（External Table）上的列创建Column Group；

ü 一个数据表中创建的拓展列数目不能超过20和数据表10%非虚拟列的数目；

ü 一个拓展列组中包括了列数目位于2-32的范围内；

ü 一个列只能出现在一个拓展列组中；

ü 列组中不能包括表达式；

ü compatible参数必须在11.0.0.0以上；

最后，个人感觉在实际中，特别是开发环境下很少会使用到column group的功能。因为解决执行计划问题的手段很多，column group不是最优的方法。而在运维环境中，常常会遇到书写很糟糕的SQL和设计。此时运维人员通常没有机会修改SQL源代码。所以，column group作为一种运维手段，是可以进行尝试的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-710952/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17203031/viewspace-710952/