oracle 索引函数--sql性能调优

      函数索引正确恰当使用也能成倍提高查询速度。 Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型（后面简称为FBI）。有了这个特性后，Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数，也可以使用户自己的PL/SQL函数等。   DBA在SQL语句调优的过程中遇到的一个很常见的问题就是，如何优化那些在WHERE子句中使用了函数的语句。因为在以前，在WHERE子句中使用函数会使在这个表上创建的索引没法利用，从而难以提高这个语句的性能。   函数索引介绍与使用：对于函数索引（functional index）， 它是定义在一个函数的结果上的，这个函数对某一个表的一个或多 个字段进行操作。函数索引可 以用于获得对那些基于函数调用结果数据的快速访问． 比如，做大小写无关比较的常用方法是使用函数 lower∶ SELECT * FROM test1 WHERE lower(col1) = 'value'; 如果我们在 lower(column) 操作的结果上定义索引， 那么这个查询就可以使用索引∶   CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));   在索引定义里的函数可以接受更多个参数，但是这些参数 都必须是表的字段，而不能是常量．函数索引总是单字段 索引(也就是函数的结果)，即使函数有多个输入域也如此； 不可能有包含函数调用的多字段索引．   上面那段提到的约束可以很容易通过在索引定义中使用 客户化定义的函数，由该函数在内部计算任何我们需要的结果来绕开．   使用基于函数的索引(FBI)时，需要先设置初始化参数query_rewrite_enabled=TRUE(默认为false) 该参数在init.ora里设置，以oracle 9i2为例，init.ora文件路径为D:/oracle/admin/mydb/pfile,我这里把oracle装在D盘，mydb是我的数据库.   案例：   使用基于成本的优化器，索引为标准的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（DBA_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 <= 256;   复合索引的例子：       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的才可以。