[20160427]建立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.
--建立函数索引看看.
SCOTT@book> create index if_t_ab on t(a,trunc(b));
Index created.
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
--可以发现建立函数索引后才可以建立相关Extended Statistics.
SCOTT@book> exec dbms_stats.drop_extended_stats(user,'T','(a,trunc(b))');
BEGIN dbms_stats.drop_extended_stats(user,'T','(a,trunc(b))'); END;
*
ERROR at line 1:
ORA-20000: extension "(a,trunc(b))" does not exist
ORA-06512: at "SYS.DBMS_STATS", line 8755
ORA-06512: at "SYS.DBMS_STATS", line 33459
ORA-06512: at line 1
--可以发现这样建立了无法删除.删除函数索引看看.
SCOTT@book> drop index if_t_ab ;
Index dropped.
SCOTT@book> exec dbms_stats.drop_extended_stats(user,'T','(a,trunc(b))');
PL/SQL procedure successfully completed.
--换一句话讲你要建立某个函数+字段的相关扩张统计,必须绕一个弯,先建立索引,才可以建立.
--删除函数索引后扩展统计并不消失.
3.进一步重复测试:
SCOTT@book> insert into t values (1,sysdate);
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select owner,table_name,column_name,data_type,data_default from DBA_TAB_COLS where owner=user and table_name='T';
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE DATA_DEFAULT
------ ---------- -------------------- ---------- ---------------
SCOTT T B DATE
SCOTT T A NUMBER
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
--可以发现在建立函数索引时自动建立函数索引中里面函数作为hidden_column,virtual_column .
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
SCOTT@book> column data_default format a40
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_STUE4B2X1G802ME0XHTBYWFY_Q NUMBER SYS_OP_COMBINED_HASH("A",TRUNC("B")) YES YES
SCOTT T SYS_NC00003$ DATE TRUNC("B") YES YES
SCOTT T B DATE NO NO
SCOTT T A NUMBER NO NO
SCOTT@book> drop index if_t_ab ;
Index dropped.
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_STUE4B2X1G802ME0XHTBYWFY_Q NUMBER SYS_OP_COMBINED_HASH("A",TRUNC("B")) YES YES
SCOTT T B DATE NO NO
SCOTT T A NUMBER NO NO
--可以发现删除索引后,某个函数+字段的相关扩张统计还在.其他版本没有环境无法测试.留给大家测试.