Oracle扩展的统计信息

我们在收集列的统计信息与直方图时，往往都是对某一列的收集。当谓词使用多个相关列时，会导致约束条件的冗余。这几个相关的列也被称作关联列。出现这种情况时，查询优化器也会做出不准确的判断。所以我们必须对这些相关列收集统计信息或直方图来描述这种依赖关系。

幸运的是，从Oracle11g开始，数据库可以收集基于表达式或者一组列上的对象统计信息和直方图，从而解决这种问题。这种新的统计叫做扩展的统计信息（extension statistics）。

这种技术实际上是基于表达式或一组列创建一个隐藏列，叫做扩展（extension），再在扩展列上收集统计信息与直方图。

一、如何定义扩展列

可以调用Oracle自带的包dbms_stats的函数create_extended_stats来实现。下面对测试表的相关列做扩展列。测试表语句参见《Oracle中收集表与列统计信息》（http://www.linuxidc.com/Linux/2013-12/93503.htm）一个基于表达式upper(pad)，另一个基于val2和val3组成的列组。在测试表里，val2和val3取值相同，高度关联。

SELECT DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME  => 'TEST',
                                        TABNAME  => 'T',
                                        EXTENSION => '(upper(pad))'),
      DBMS_STATS.CREATE_EXTENDED_STATS(OWNNAME  => 'TEST',
                                        TABNAME  => 'T',
                                        EXTENSION => '(val2,val3)')
  FROM DUAL;

 

这样就定义了两个扩展列。他们分别是基于表达式的和基于多列的。

二、如何查询扩展列信息

基于user_stat_extensions、dba_stat_extensions和all_stat_extensions，都能查询相关的扩展列信息。

SELECT COLUMN_NAME, DATA_TYPE, HIDDEN_COLUMN, DATA_DEFAULT
  FROM USER_TAB_COLS
 WHERE TABLE_NAME = 'T';

COLUMN_NAME                              DATA_TYPE  HID DATA_DEFAULT
---------------------------------------- ---------- --- ----------------------------------------
ID                                      NUMBER    NO
VAL1                                    NUMBER    NO
VAL2                                    NUMBER    NO
VAL3                                    NUMBER    NO
PAD                                      VARCHAR2  NO
SYS_STU0KSQX64#I01CKJ5FPGFK3W9          VARCHAR2  YES UPPER("PAD")
SYS_STUPS77EFBJCOTDFMHM8CHP7Q1          NUMBER    YES SYS_OP_COMBINED_HASH("VAL2","VAL3")

 

从data_default这列我们可以观察到，SYS_OP_COMBINED_HASH("VAL2","VAL3")，扩展列统计使用了哈希函数，所以val2和val3只有使用相等（=）谓词时，优化器才使用扩展统计信息。

二、如何删除扩展统计信息

依然使用Oracle自带的dbms_stats提供的过程drop_extended_stats来删除扩展统计信息。

BEGIN
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME  => 'TEST',
                                TABNAME  => 'T',
                                EXTENSION => '(upper(pad))');
  DBMS_STATS.DROP_EXTENDED_STATS(OWNNAME  => 'TEST',
                                TABNAME  => 'T',
                                EXTENSION => '(val2,val3)');
END;

 

最后提一下，扩展统计信息是基于Oracle11g的另一个新特性——虚拟列。它并不存储数据，那它有什么现实意义呢？我们可以设想，在开发代码中，有很多sql语句用到了upper(varchar2)、trunc(date)，此时尽管在这些列上建立索引，执行计划依然不会走索引，为了避免全表扫描，我们最好的方法是改写语法，谓词尽量不被函数转换，但有时候在不好转换语句时，可以创建一个虚拟列，然后在虚拟列上建立索引。比如下面的方法：

CREATE TABLE persons(
NAME VARCHAR2(100),
name_upper AS (UPPER(NAME)));

 

如果在频繁查询使用了upper(name)=’MIKE’,就可以使用name_upper=’MIKE’,前提是虚拟列建立索引。当然虚拟列也不不好的地方，比如插入数据不能指定所有列，因为虚拟列是不存数据的。