[20160429]建立Extended Statistics 和函数索引问题.txt

--11G支持相关数据的统计分析,比如如果两个字段存在相关性通过分析,能够得到更加良好的统计信息,以及生成好的执行计划.

--但是如果结合函数索引呢?通过一个简单的例子来说明:

--前次做的测试:

1.环境:

SCOTT@book> @ &r/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

SCOTT@book> create table t (a number, b date);

Table created.

2.建立Extended Statistics:

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,trunc(b))') from dual;

select dbms_stats.create_extended_stats(user,'T','(a,trunc(b))') from dual        * ERROR at line 1: ORA-20001: Invalid Extension: Column group can contain only columns seperated by comma

--可以看出建立 Column group仅仅包含字段,使用,隔开.

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,b)') from dual;

DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T','(A,B)') -------------------------------------------------- SYS_STUNA$6DVXJXTP05EH56DTIR0X

SCOTT@book> exec dbms_stats.drop_extended_stats(user,'T','(a,b)') ;

PL/SQL procedure successfully completed.

--上次我通过建立索引来实现函数+字段的Extended Statistics ,如果仔细看前面的提示建立索引后建立了一个hidden以及虚拟列

--SYS_NC00003$,如果我先建立一个虚拟列应该也可以实现.测试:

--建立虚拟列:

SCOTT@book> alter table t add (c date generated always as (trunc(b))); Table altered.

SCOTT@book> select owner,table_name,column_name,data_type,data_default,hidden_column,virtual_column from DBA_TAB_COLS where owner=user and table_name='T';

OWNER  TABLE_NAME COLUMN_NAME          DATA_TYPE  DATA_DEFAULT         HID VIR ------ ---------- -------------------- ---------- -------------------- --- --- SCOTT  T          C                    DATE       TRUNC("B")           NO  YES SCOTT  T          B                    DATE                            NO  NO SCOTT  T          A                    NUMBER                          NO  NO

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,trunc(b))') c40 from dual;

C40 ---------------------------------------- SYS_STU021V7H9RNW$DK6GQ1ZFI5B#

--OK!不过明显跟以前定义的名字不一样.前面测试如下:

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,trunc(b))') from dual; DBMS_STATS.CREATE_EXTENDED_STATS(USER,'T','(A,TRUNC(B))') ---------------------------------------------------------- SYS_STUE4B2X1G802ME0XHTBYWFY_Q

--SYS_STUE4B2X1G802ME0XHTBYWFY_Q 与 SYS_STU021V7H9RNW$DK6GQ1ZFI5B# 不同.

SCOTT@book> select owner,table_name,column_name,data_type,data_default,hidden_column,virtual_column from DBA_TAB_COLS where owner=user and table_name='T';

OWNER  TABLE_NAME COLUMN_NAME                    DATA_TYPE  DATA_DEFAULT                             HID VIR ------ ---------- ------------------------------ ---------- ---------------------------------------- --- --- SCOTT  T          SYS_STU021V7H9RNW$DK6GQ1ZFI5B# NUMBER     SYS_OP_COMBINED_HASH("A",TRUNC("B"))     YES YES SCOTT  T          C                              DATE       TRUNC("B")                               NO  YES SCOTT  T          B                              DATE                                                NO  NO SCOTT  T          A                              NUMBER                                              NO  NO

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,c)') c40 from dual;

select dbms_stats.create_extended_stats(user,'T','(a,c)') c40 from dual        * ERROR at line 1: ORA-20001: Error when processing extension -  virtual column is referenced in a column expression ORA-06512: at "SYS.DBMS_STATS", line 8676 ORA-06512: at "SYS.DBMS_STATS", line 33335

--可以看出实际上建立的(a,c)的Extended Statistics.

SCOTT@book> exec dbms_stats.drop_extended_stats(user,'T','(a,trunc(b))');

PL/SQL procedure successfully completed.

--而删除后如用如下建立Extended Statistics

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,c)') c40 from dual; select dbms_stats.create_extended_stats(user,'T','(a,c)') c40 from dual        * ERROR at line 1: ORA-20001: Error when processing extension -  virtual column is referenced in a column expression ORA-06512: at "SYS.DBMS_STATS", line 8676 ORA-06512: at "SYS.DBMS_STATS", line 33335

--很明显不能包含virtual column.

SCOTT@book> alter table t drop column c;

Table altered.

SCOTT@book> create index if_t_ab on t(a,trunc(b));

Index created.

SCOTT@book> select owner,table_name,column_name,data_type,data_default,hidden_column,virtual_column from DBA_TAB_COLS where owner=user and table_name='T';

OWNER  TABLE_NAME COLUMN_NAME                    DATA_TYPE  DATA_DEFAULT                             HID VIR ------ ---------- ------------------------------ ---------- ---------------------------------------- --- --- SCOTT  T          SYS_NC00003$                   DATE       TRUNC("B")                               YES YES SCOTT  T          B                              DATE                                                NO  NO SCOTT  T          A                              NUMBER                                              NO  NO

SCOTT@book> select dbms_stats.create_extended_stats(user,'T','(a,SYS_NC00003$)') c40 from dual;

select dbms_stats.create_extended_stats(user,'T','(a,SYS_NC00003$)') c40 from dual        * ERROR at line 1: ORA-20001: Error when processing extension -  virtual column is referenced in a column expression ORA-06512: at "SYS.DBMS_STATS", line 8676 ORA-06512: at "SYS.DBMS_STATS", line 33335

--一样不行.