发表于: 2008.04.16 22:29
分类: 性能优化
出处: http://warehouse.itpub.net/post/777/460016
---------------------------------------------------------------
在创建函数index之后系统会自动产生虚拟列，而这一列也是隐藏的，在创建index时系统自动使用了compute statistics选项为创建的index搜集了statistics，然而并不会为生成的虚拟列搜集statistics，这时如果需要为虚拟列搜集statistics就需要重新分析表或者单独为虚拟列搜集statistics。
SQL> create table tt(id int , name char(1));
表已创建。
SQL> insert into tt values(1 , 'a');
已创建 1 行。
SQL> insert into tt values(2 , 'A');
已创建 1 行。
SQL> insert into tt values(3 , 'B');
已创建 1 行。
SQL> insert into tt values(4 , 'b');
已创建 1 行。
SQL> commit;
提交完成。
SQL> exec dbms_stats.gather_table_stats('XYS','TT');
PL/SQL 过程已成功完成。
SQL> select column_name
2 from user_tab_col_statistics
3 where table_name='TT';
COLUMN_NAME
------------------------------
ID
NAME
SQL> create index idx_tt on tt(upper(name));
索引已创建。
SQL> select table_name,column_name,hidden_column,virtual_column
2 from user_tab_cols
3 WHERE table_name='TT';
TABLE_NAME COLUMN_NAME HID VIR
------------------------------ ------------------------------ --- ---
TT SYS_NC00003$ YES YES
TT NAME NO NO
TT ID NO NO
--显然SYS_NC00003$是生成的虚拟列
SQL> exec dbms_stats.gather_index_stats('XYS','IDX_TT');
PL/SQL 过程已成功完成。
SQL> select column_name
2 from user_tab_col_statistics
3 where table_name='TT';
COLUMN_NAME
------------------------------
ID
NAME
--搜集index的statistics之后，虚拟列的statistics并没有被搜集
SQL> exec dbms_stats.gather_table_stats('XYS','TT');
PL/SQL 过程已成功完成。
SQL> select column_name
2 from user_tab_col_statistics
3 where table_name='TT';
COLUMN_NAME
------------------------------
ID
NAME
SYS_NC00003$
--重新分析表之后发现虚拟列的statistics被搜集了
--插入验证数据
SQL> insert into tt values(5 , 'c');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select column_name,num_distinct
2 from user_tab_col_statistics
3 where table_name='TT';
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 4
NAME 4
SYS_NC00003$ 2
--单独为虚拟列搜集statistics，因为我们清晰的看到尽管现在是5条数据，但是执行下面过程之后仅仅
是虚拟列SYS_NC00003$所对应 的NUM_DISTINCT由2变成了3，而id和name的NUM_DISTINCT都是4而不是5，
原因是使用了参数METHOD_OPT=>'for all hidden columns size AUTO'，METHOD_OPT的default值是'for
all columns,因此通过使用参数METHOD_OPT=>'for all hidden columns size AUTO'可以单独为函数索引
所生成的虚拟列搜集statistics
SQL> exec dbms_stats.gather_table_stats(ownname=>'XYS',tabname=>'TT',METHOD_OPT=
>'for all hidden columns size AUTO');
PL/SQL 过程已成功完成。
SQL> select column_name,num_distinct
2 from user_tab_col_statistics
3 where table_name='TT';
COLUMN_NAME NUM_DISTINCT
------------------------------ ------------
ID 4
NAME 4
SYS_NC00003$ 3
SQL> select * from tt;
ID N
---------- -
1 a
2 A
3 B
4 b
5 c
SQL>
