0427建立Extended Statistics函式索引問題

[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

--可以發現刪除索引後,某個函式+欄位的相關擴張統計還在.其他版本沒有環境無法測試.留給大家測試.