函数索引:
1、创建函数索引:
1)、CREATE INDEX IDX_EMPLOYEE_NAME ON EMPLOYEE(UPPER(NAME));
SELECT * FROM EMPLOYEE WHERE UPPER(NAME) = 'PANDA';
2)、CREATE INDEX IDX_WEATHER ON WEATHER((MAXTEMP-MINTEMP) DESC,MAXTEMP);
SELECT * FROM WEATHER
WHERE ((MAXTEMP-MINTEMP)<20 AND MAXTEMP>40);
3)、CREATE INDEX IDX_SCORE ON SCORE((Chinese+math+english)/3);
SELECT * FROM SCORE WHERE (Chinese+math+english)/3>90;
2、限制
1)、索引表达式的数据类型不能是VARCHAR2,RAW,LONG RAW,或不定长度的PL/SQL数据类型
2)、数据库做OR展开时,会忽略函数的索引
3)、索引表达式不能调用聚合函数,sum,count
4)、用自定义的函数建立索引时,函数必须是确定性的,要在函数的定义中包括DETERMINSTIC
(在函数中包含这个关键字,oracle会认为你已经尽职地核实了,如果这个函数时非确定性的函数oracle不会捕获这种错误)
3、收集统计信息
create table employee(
id integer,
name varchar2(30),
salary integer,
bonus integer,
ratio number(4,4));
insert into employee
select
rownum id,
'huang_'||rownum name,
rownum salary,
rownum*10 bonus,
rownum/1000 ratio
from dual connect by rownum<1000;
HUANG>>>>select * from employee;
ID NAME SALARY BONUS RATIO
---------- -------------------- ---------- ---------- ----------
247 huang_247 247 2470 .247
248 huang_248 248 2480 .248
249 huang_249 249 2490 .249
250 huang_250 250 2500 .25
251 huang_251 251 2510 .251
252 huang_252 252 2520 .252
253 huang_253 253 2530 .253
254 huang_254 254 2540 .254
...
EXEC dbms_stats.gather_table_stats(ownname=>'HUANG',TABNAME=>'EMPLOYEE',CASCADE=>TRUE,method_opt=>'FOR ALL COLUMNS');
SELECT COLUMN_NAME,NUM_DISTINCT,HIDDEN_COLUMN,VIRTUAL_COLUMN FROM DBA_TAB_COLS WHERE TABLE_NAME='EMPLOYEE'
COLUMN_NAME NUM_DISTINCT HIDDEN VIRTUA
---------------------------
------------ ------ ------
RATIO
999
NO NO
BONUS
999
NO NO
SALARY
999
NO NO
NAME
999
NO NO
ID
999
NO NO
oracle在创建一个函数索引时,会在父表中创建一个隐藏的虚拟列。数据库在表中创建这个虚拟列
是为了帮助优化器更准确地确定函数的选择性和基数,从而帮助它更准确地使用已经创建的基于函数
的索引的成本。
当创建基于函数的索引时,ORACLE会自动计算索引的统计信息,如叶块的数量,BLEVEL和聚簇因子,
但不会计算其他更重要的信息,如与虚拟列关联的不同值的个数(NDV)
CREATE INDEX IDX_EMPLOYEE_INCOME ON EMPLOYEE(SALARY*12+BONUS*RATIO);
SELECT COLUMN_NAME,NUM_DISTINCT,HIDDEN_COLUMN,VIRTUAL_COLUMN FROM DBA_TAB_COLS WHERE TABLE_NAME='EMPLOYEE'
COLUMN_NAME NUM_DISTINCT HIDDEN VIRTUA
-------------------------------
------------ ------ ------
SYS_NC00006$
YES YES
RATIO
999
NO NO
BONUS
999
NO NO
SALARY
999
NO NO
NAME
999
NO NO
ID
999
NO NO
数据库在创建基于函数的索引时,还创建了一个新的虚拟列(SYS_NC00006$)。此列是虚拟和隐藏的。
NUM_DISTINCT为空,意味着数据库不知道这个基于函数的索引的选择性。基于成本的优化器可能因此
生成了错误的执行计划,即使它用的是最新的函数索引。为了避免这个问题创建了基于函数的索引后,
必须马上收集隐藏的虚拟列的统计信息。
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>NULL,TABNAME=>'EMPLOYEE',ESTIMATE_PERCENT=>NULL,CASCADE=>TRUE,METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE 1');
COLUMN_NAME NUM_DISTINCT HIDDEN VIRTUA
---------------------------------
------------ ------ ------
SYS_NC00006$
999 YES YES
RATIO
999 NO NO
BONUS
999 NO NO
SALARY
999 NO NO
NAME
999 NO NO
ID
999 NO NO
也可以直接收集函数表达式的统计信息
exec dbms_stats.gather_table_stats(ownname=>'HUANG',TABNAME=>'EMPLOYEE',METHOD_OPT=>'FOR ALL COLUMNS FOR COLUMNS(SALARY*12+BONUS*RATIO)');
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/27003343/viewspace-762268/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/27003343/viewspace-762268/