函数索引

来源：互联网发布：网络打印机显示错误编辑：程序博客网时间：2024/06/06 02:02

函数索引：

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)');