Oracle：函数索引
如何创建oracle函数索引Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型（后面简称为FBI）。
有了这个特性后，Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数，
也可以使用户自己的PL/SQL函数等。

DBA在SQL语句调优的过程中遇到的一个很常见的问题就是，如何优化那些在WHERE子句中使用了函数的语句。
因为在以前，在WHERE子句中使用函数会使在这个表上创建的索引没法利用，从而难以提高这个语句的性能。
例子：

使用基于成本的优化器，索引为标准的B树索引，建立在SURNAME列上。
SQL>create index non_fbi on sale_contacts (surname);
SQL>analyze index non_fbi compute statistics;
SQL>:analyze table sale_contacts compute statistics;
SQL>SELECT count(*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';

Execution Plan
----------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272)
从SQL*PLUS的autotrace产生的执行路径可以看到，虽然我们在WHERE子句中用到的SURNAME列上创建了索引，但是仍然执行的是全表扫描。如果这张表很大的话，这回消耗大量的时间。
现在我们试着建立一个FBI索引：
SQL>create index fbi on sale_contacts (UPPER(surname));
SQL>analyze index fbi compute statistics;
SQL>analyze table sale_contacts compute statistics;
SQL>SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON';
Execution Plan
----------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477)

从SQL*Plus返回的执行计划我们可以看到，这次，Oracle对表不再全表扫描，而是先扫描索引，因为优化器可以知道FBI索引得存在。
使用FBI索引所能够带来的性能提升取决于表的大小、表中重复记录的量、在WHERE子句中使用的列等因素。
有一点需要清楚，FBI索引并不真正在索引里边存储了表达式的结果，而是使用了一个“表达树”（expression tree）。

由优化器来对SQL语句中的表达式进行解析，并且和FBI索引上面的表达式进行对比。这里，SQL函数的大小写时敏感的。
因此要求SQL语句中使用的函数和创建FBI索引得时候的那个SQL函数的大小写一致，否则无法利用这个FBI索引。
因此，在编程的时候要有一个良好的编程风格。
Init.ora里边需要修改的参数
下面这几个参数必须在init.ora里边指定：
QUERY_REWRITE_INTEGRITY = TRUSTED
QUERY_REWRITE_ENABLED = TRUE
COMPATIBLE = 8.1.0.0.0 (or higher)

授权：
要使一个用户能够创建FBI索引，他必须被授予以下权限：CREATE INDEX和QUERY REWRITE,或者CREATE ANY INDEX和GLOBAL QUERY REWRITE这两个权限。
索引的使用者必须能够有那个FBI索引上使用的那个函数的执行权限。如果没有相应的权限，那么这个FBI索引得状态将变成DISABLED—BA_INDEXES）。
如果那个FBI索引得状态是DISABLED，那么DBA可以这样来处理：
a：删除并重建
B：ALTER INDEX index_name ENABLED。这个Enabled只能对FBI索引使用。
C：ALTER INDEX UNUSABLE;
注意：如果一个查询中使用到了这个索引，但是这个FBI索引的状态是DISABLED，但是优化器选择了使用这个索引，那么将会返回一个Oracle错误。
例子：
ORA error:
ERROR at line 1: ORA-30554: function-based index MYUSER.FBI is disabled.
而且，一旦这个FBI索引的状态是Disabled，那么这张表上所有涉及索引列的DML操作也将失败。除非这个索引得状态变成UNUSABLE，而且在初始化参数里边指定SKIP_UNUSABLE_INDEXES为TRUE。

一些例子：

SQL>CREATE INDEX expression_ndx
ON mytable ((mycola + mycolc) * mycolb);

SQL>SELECT mycolc FROM mytable
WHERE (mycola + mycolc) * mycolb

复合索引的例子：

SQL>CREATE INDEX example_ndx
ON myexample (mycola, UPPER(mycolb), mycolc);

SQL>SELECT mycolc FROM myexample
WHERE mycola = 55 AND UPPER(mycolb) = 'JONES';

限制和规则总结：

对于下面这些限制，不能创建FBI索引：
a) LOB 列
b) REF
c) Nested table 列
d) 包含上面数据类型的对象

FBI索引必须遵守下面的规则：

a) 必须使用基于成本的优化器，而且创建后必须对索引进行分析
b) 不能存储NULL值。因为任何函数在任何情况下都不能返回NULL值。
c)如果一个用户定义的PL/SQL例程失效了，而且这个例程被FBI索引用到了，那么相应的这个FBI索引会变成DISABLED
d)创建FBI索引得函数必须是确定性的。即，对于指定的输入，总是会返回确定的结果。
e) 索引的属主如果没有了在FBI索引里面使用的函数的执行权限，那么这个FBI索引会变成DISABLED.
f) 在创建索引得函数里面不能使用SUM等总计函数。
g)要把一个DISABLED了的索引重新变成ENABLED，这个函数必须首先是ENABLED的才可以。

================================================================================================这个语句还能优化嘛？

原语句：
select b.*
? ? from test1 a,test2 b
? ?where a.sflag ='-3'
? ???and instr(','||a.id||',',','||b.certiid||',')>0;

? ?通过执行发现test2表执行了全表扫描，需要120秒通过分析发现是test2的certiid列上实际上是有索引的。
但是它不能使用这个索引，不能通过索引的ROWID来搜索表，因为这里并没有b.certiid等于的条件，而是通过
条件instr(','||a.id||',',','||b.certiid||',')>0进行的连接。
? ?然后考虑到需要返回的值是B.*而索引只包含了CERTIID列，索引INDEX FAST SCAN也用不到。
? ?所以我们这里考虑修改如下：
? ?select * from??test2 where certiid in(
? ? select b.certiid
? ? from test1 a,test2 b
? ???where a.sflag ='-3'
? ???and instr(','||a.id||',',','||b.certiid||',')>0);
? ???
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID??gsha1gj68gacg, child number 0
-------------------------------------
select /*+??gather_plan_statistics */??*? ?from test2??where certiid in? ?? ???(select
b.certiid? ?? ?? ???from test1 a, test2 b? ?? ?? ? where a.sflag = '-3'? ?? ?? ?? ?and
instr(',' || a.id || ',', ',' || b.certiid || ',') > 0)

Plan hash value: 4074250259

----------------------------------------------------------------
| Id??| Operation? ?? ?? ?? ?? ?? ?? ???| Name? ?? ?? ?? ?| Starts | E-Rows | A-Rows |? ?A-Time? ?| Buffers |
----------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------
|? ?0 | SELECT STATEMENT? ?? ?? ?? ?? ? |? ?? ?? ?? ?? ???|? ?? ?1 |? ?? ???|? ???54 |00:00:32.69 |? ???133K|
|? ?1 |??NESTED LOOPS? ?? ?? ?? ?? ?? ? |? ?? ?? ?? ?? ???|? ?? ?1 |? ?? ?1 |? ???54 |00:00:32.69 |? ???133K|
|? ?2 |? ?VIEW? ?? ?? ?? ?? ?? ?? ?? ???| VW_NSO_1? ?? ???|? ?? ?1 |? ?? ?1 |? ???54 |00:00:32.69 |? ???133K|
|? ?3 |? ? HASH UNIQUE? ?? ?? ?? ?? ?? ?|? ?? ?? ?? ?? ???|? ?? ?1 |? ?? ?1 |? ???54 |00:00:32.69 |? ???133K|
|? ?4 |? ???NESTED LOOPS? ?? ?? ?? ?? ? |? ?? ?? ?? ?? ???|? ?? ?1 |? ?? ?1 |? ???54 |00:00:28.67 |? ???133K|
|? ?5 |? ?? ?TABLE ACCESS BY INDEX ROWID| test1? ?? ?|? ?? ?1 |? ?? ?1 |? ???38 |00:00:00.01 |? ?? ?49 |
|*??6 |? ?? ? INDEX RANGE SCAN? ?? ?? ? | INDEX_SFLAG? ???|? ?? ?1 |? ?? ?1 |? ???38 |00:00:00.01 |? ?? ? 3 |
|*??7 |? ?? ?INDEX FAST FULL SCAN? ?? ? | PK_test2 |? ???38 |??24575 |? ???54 |00:00:25.65 |? ???133K|
|? ?8 |? ?TABLE ACCESS BY INDEX ROWID? ?| test2? ? |? ???54 |? ?? ?1 |? ???54 |00:00:00.01 |? ???167 |
|*??9 |? ? INDEX UNIQUE SCAN? ?? ?? ?? ?| PK_test2 |? ???54 |? ?? ?1 |? ???54 |00:00:00.01 |? ???113 |
----------------------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

? ?6 - access("A"."SFLAG"='-3')
? ?7 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)
? ?9 - access("CERTIID"="$nso_col_1")
? ?这里使用了一个子查询，子查询中使用的条件不是B.*而是B.certiid.可以看到这里使用了 INDEX FAST FULL SCAN，相当于把
索引当表来进行扫描，不考虑索引的结构。但是这里的一列的‘表’比所有字段的表要小得多所以也加快了查询。
? ? 修改后得语句任然有性能问题，看到这里的
? ? INDEX FAST FULL SCAN? ?? ? | PK_test2 |? ???38
? ? 在NESTED LOOP的机制下运行了38次。本来想通过HASH JION来代替NESTED LOOPS 但是这里条件是INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0，所以不好修改了。
? ?修改后语句运行的时间由120秒降低到48秒，不知道还有优化的方法没？
原执行计划：
PLAN_TABLE_OUTPUT
--------------------------------------------
Plan hash value: 1212030027
--------------------------------------------
| Id??| Operation? ?? ?? ?? ?? ?? ???| Name? ?? ?? ?| Rows??| Bytes | Cost (%CPU
--------------------------------------------
|? ?0 | SELECT STATEMENT? ?? ?? ?? ? |? ?? ?? ?? ???|??3943K|??1921M|??1384K??(1
|? ?1 |??NESTED LOOPS? ?? ?? ?? ?? ? |? ?? ?? ?? ???|??3943K|??1921M|??1384K??(1
|? ?2 |? ?TABLE ACCESS BY INDEX ROWID| test1? ?|? ? 71 |??1278 |? ???5? ?(0
|*??3 |? ? INDEX RANGE SCAN? ?? ?? ? | INDEX_SFLAG??|? ? 71 |? ?? ? |? ???3? ?(0
|*??4 |? ?TABLE ACCESS FULL? ?? ?? ? | test2 | 55470 |? ? 26M| 19504? ?(1
--------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
? ?3 - access("A"."SFLAG"='-3')
? ?4 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)

17 rows selected

http://www.itpub.net/thread-1477884-1-9.html