Oracle index】SQL语句利用函数索引注意点



在 【Oracle index】SQL语句无法走索引的一些情况分析及语句改写思路  这里列出了一些SQL语句无法利用现有索引的情况，特别是不好的SQL写法，导致对列进行了相关运算，比如数学运算col+5,函数运算to_char(done_date,'yyyy-mm-dd')等等，导致索引失效。当然，解决此类问题的最好办法，是避免对需要使用索引的列进行相关运算，而保持列最原始的状态（在索引创建未使用函数情况下）。但是，函数索引也是非常常见的。

 
     函数索引（FBI）还是很常见的，比如create index idx_t on t(to_char(done_date,'yyyy-mm-dd'));是函数索引，create index idx_t on t(status,0)是函数索引（这个较特别，使用一个常数伪列，在需要将NULL存储到BTREE INDEX中使用）。函数索引有一点要特别注意，在使用函数索引的时候，SQL语句中的对应表达式必须与创建函数索引的表达式完全一致（当然，空格、关键字大小写的可以忽略），如果不是完全一致，则也利用不上函数索引。下面举两个例子说明：
1.创建CASE WHEN函数索引
 比如表t,其中status有2个值，VALID和INVALID表示状态，其中INVALID的占比较少，为了减少索引的存储空间，可以将status='INVALID'的纳入到索引中，这时候就需要使用case when或decode...

dingjun123@ORADB> CREATE TABLE t   2  AS   3  SELECT * FROM dba_objects; Table created. Elapsed: 00:00:00.88 dingjun123@ORADB> CREATE INDEX IDX_T ON T(CASE WHEN STATUS='INVALID' THEN 1 ELSE NULL END); Index created. Elapsed: 00:00:01.18 dingjun123@ORADB> set autotrace traceonly exp --和建立函数索引的表达式一致，可以使用索引 dingjun123@ORADB> SELECT *   2  FROM t   3  WHERE CASE WHEN STATUS = 'INVALID'   4                       THEN 1   5                       ELSE NULL   6                       END = 1; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1594971208 ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |  1051 |   100K|   146   (0)| 00:00:02 | |   1 |  TABLE ACCESS BY INDEX ROWID| T     |  1051 |   100K|   146   (0)| 00:00:02 | |*  2 |   INDEX RANGE SCAN          | IDX_T |  1051 |       |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(CASE "STATUS" WHEN 'INVALID' THEN 1 ELSE NULL END =1) --和建立函数索引的表达式不一致，索引失效，少了ELSE NULL dingjun123@ORADB> SELECT *   2  FROM t   3  WHERE CASE WHEN STATUS = 'INVALID'   4                       THEN 1   5                       END = 1; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   753 | 73794 |   302   (1)| 00:00:04 | |*  1 |  TABLE ACCESS FULL| T    |   753 | 73794 |   302   (1)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(CASE "STATUS" WHEN 'INVALID' THEN 1 END =1)

    建立这样的函数索引是常见的，但是可能出现这样的情况：要么建立索引的时候没有加ELSE NULL，或者加了，但是在写语句的时候有的加了ELSE NULL，有的未加，导致函数索引表达式与SQL语句里的表达式不完全一致，导致索引失效。所以，有必要对函数索引的创建以及SQL语句使用函数索引，建立规范，特别是要严格测试。

2.对日期列建立函数索引

dingjun123@ORADB> drop table t; Table dropped. Elapsed: 00:00:00.25 dingjun123@ORADB> CREATE TABLE t   2  AS   3  SELECT * FROM dba_objects; Table created. Elapsed: 00:00:00.84 dingjun123@ORADB> CREATE INDEX idx_t ON t(TO_CHAR(last_ddl_time,'YYYY-MM-DD')); Index created. Elapsed: 00:00:00.68 dingjun123@ORADB> exec dbms_stats.gather_table_stats(ownname => USER,tabname => 'T',estimate_percent => 100,cascade => TRUE); PL/SQL procedure successfully completed. Elapsed: 00:00:03.96 --语句中的fmt大小写与索引创建的表达式fmt不一样，导致走不了索引 dingjun123@ORADB> SELECT *   2  FROM t   3  WHERE TO_CHAR(last_ddl_time,'yyyy-mm-dd')='2013-5-20'; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |      |   753 | 81324 |   303   (2)| 00:00:04 | |*  1 |  TABLE ACCESS FULL| T    |   753 | 81324 |   303   (2)| 00:00:04 | -------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(TO_CHAR(INTERNAL_FUNCTION("LAST_DDL_TIME"),'yyyy-mm-dd')='               2013-5-20') --完全一样，走索引 dingjun123@ORADB> SELECT *   2  FROM t   3  WHERE TO_CHAR(last_ddl_time,'YYYY-MM-DD')='2013-5-20'; Elapsed: 00:00:00.00 Execution Plan ---------------------------------------------------------- Plan hash value: 1594971208 ------------------------------------------------------------------------------------- | Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |       |   291 | 31428 |     7   (0)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID| T     |   291 | 31428 |     7   (0)| 00:00:01 | |*  2 |   INDEX RANGE SCAN          | IDX_T |   291 |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    2 - access(TO_CHAR(INTERNAL_FUNCTION("LAST_DDL_TIME"),'YYYY-MM-DD')='2013-               5-20')

   第2个例子，很多情况下是不需要使用函数索引的，可以用where last_ddl_time>=to_date... and last_ddl_time<to_date...来实现。这里举例只是说明，使用函数索引的时候，要特别注意，最好严格保证函数索引的表达式结构与对应的内容，比如fmt,与索引创建表达式完全一致，做好测试和规范，才能避免相关问题的发生。