无法使用到函数索引的情况

无法使用到函数索引的情况

2.1必须是在CBO模式下
具体举例来说明
SQL> create table test as select * from zhjs_app.tg_log where rownum<=500;
Table created
SQL> create index IDX_TG_LOG_BATCH_CODE on test (SUBSTR(BATCH_CODE,6,6));
Index created
表和函数索引索引建好后，看看是否能用到索引
SQL> explain plan for select count(*) from test where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
查询发现始终无法用到该索引
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT AGGREGATE      |             |       |       |       |
|*  2 |   TABLE ACCESS FULL  | TEST        |       |       |       |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------------------
   2 - filter(SUBSTR("TEST"."BATCH_CODE",6,6)='010102')
Note: rule based optimization
15 rows selected
究竟怎么回事？这里我要重点说明一下，函数索引要满足两个条件，一个是查询要基于COST的，才能生效，第二个是query_rewrite_enabled  这个参数要是true,第二个条件到9i以后并不怎么必要了，关键是第一点。
试着给这个查询加上一个索引的HINT，看系统能走索引吗？
SQL> explain  plan for select /*+index(a,IDX_TG_LOG_BATCH_CODE)*/ count(*) from test a where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
查询真的走了索引
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost 
|   0 | SELECT STATEMENT     |                        |     1 |    17 |     1 |
|   1 |  SORT AGGREGATE      |                        |     1 |    17 |       |
|*  2 |   INDEX RANGE SCAN   | IDX_TG_LOG_BATCH_CODE  |    12 |   204 |     1 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("A"."BATCH_CODE",6,6)='010102')
Note: cpu costing is off
15 rows selected
为什么加上HINT后能建索引吗，这里涉及到了解HINT的一个常识，ORACLE的所有HINT中，除了/*+RULE*/外，加了 HINT，ORACLE都会选择基于代价的查询，也就是COST的查询方式，所以满足了我刚才提的条件，所以索引就用上了。
当然如果不用HINT，可以有另外一个选择，就是将表分析后，让ORACLE优化器自动选择走COST而不是走基于RULE的优化器模式，这样，也满足了我刚才说的条件，具体操作如下：
SQL> analyze table test compute statistics for table for all indexes for all indexed columns;
Table analyzed
SQL> explain  plan for select count(*) from test a where SUBSTR(BATCH_CODE,6,6)='010102';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation            |  Name                  | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                        |     1 |    17 |     2 |
|   1 |  SORT AGGREGATE      |                        |     1 |    17 |       |
|*  2 |   INDEX RANGE SCAN   | IDX_TG_LOG_BATCH_CODE  |   418 |  7106 |     2 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access(SUBSTR("A"."BATCH_CODE",6,6)='010102')
Note: cpu costing is off
15 rows selected
果然，该查询也走索引，达到目的！
总结：函数索引必须要在基于代价的优化器模式下才可以运行，因此想利用到函数索引，必须要对表进行分析或者是建立HINT强制让ORACLE走COST模式。我们结算项目就有不少地方用到函数索引，希望引起大家的注意！

 

2.2 函数必须是确定性的

现构造ljb_test表内容如下
  SQL> create table ljb_test(year varchar2(4));
Table created
SQL> insert into ljb_test values ('2009');
1 row inserted
SQL>  insert into ljb_test values ('2008');
1 row inserted
SQL> commit;
Commit complete
SQL> select to_date(year,'yyyy') from ljb_test;
TO_DATE(YEAR,'YYYY')
--------------------------------
2009-03-01
2008-03-01
如果要让这个to_date(year,'yyyy')建立索引该怎么做了，很多人可能会立即回答，建函数索引！
那实验一下，看建函数索引会是一种什么情况
SQL>  create index idx_ljb_test on ljb_test(to_date(year,'yyyy'));
ORA-01743: 仅能编制纯函数的索引
失败了！这个提示是否让摸不着头脑！
真正原因是如上查询是在三月份查的结果，如果是四月份，查询出的结果将会如下
SQL> select to_date(year,'yyyy') from ljb_test;
TO_DATE(YEAR,'YYYY')
----------------------------------
2009-04-01
2008-04-01
这点大家应该不难明白吧，该函数充满了不确定性！这就是函数索引无法创建的原因，函数索引是预先在索引中存储了函数索引的结果以备使用，现在这个函数值不确定了，怎么存储函数结果？
总结：通过本小节的学习，大家应该能加深了对函数索引的认识吧，建函数索引的函数必须要有确定性，返回不确定结果的函数就无法建立起来，因为不确定结果根本不能被函数索引预存储，所以要失败！我就曾经要建立一个这样不确定函数的函数索引而失败，最后选择了其它方式！

 

2.3 自定义函数需DETERMINISTIC关键字

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL>
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test as select * from user_objects ;
Table created
SQL> create or replace function f_minus1(i int)
  2  return int
  3  is
  4  begin
  5  return(i-1);
  6  end;
  7  /
Function created
建完函数后我们试着建立函数索引，发现建立失败
SQL> create index idx_ljb_test on ljb_test (f_minus1(object_id));

create index idx_ljb_test on ljb_test (f_minus1(object_id))
ORA-30553: 函数不能确定
将函数加上DETERMINISTIC关键字重建
SQL> create or replace function f_minus1(i int)
  2  return int DETERMINISTIC
  3  is
  4  begin
  5  return(i-1);
  6  end;
  7  /
Function created
现在发现加上DETERMINISTIC关键字后的自定义函数可以建立函数索引成功了！
SQL> create index idx_ljb_test on ljb_test (f_minus1(object_id));
Index created
SQL> analyze table ljb_test compute statistics for table for all indexes;
Table analyzed
SQL> explain plan for select * from ljb_test where f_minus1(object_id)=23;
Explained
验证得出该函数索引可以在语句中被正常的使用到。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3905012767
--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     3 |   273 |     2   (0)
|   1 |  TABLE ACCESS BY INDEX ROWID| LJB_TEST     |     3 |   273 |     2   (0)
|*  2 |   INDEX RANGE SCAN          | IDX_LJB_TEST |     1 |       |     1   (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("LJB"."F_MINUS1"("OBJECT_ID")=23)
14 rows selected
   总结：大家在工作中用到自定义函数的时候也很多，因为自定义函数可以直接在SQL中调用，简化代码，给编写数据库应用带来了很大的方便，但是与此同时我们要考虑SQL中用到的自定义函数是否能用的上索引，我们开发人员除了关注实现外也要同时考虑到性能，因此我们在建自定义函数的时候尽量考虑加上 DETERMINISTIC的关键字，以方便将来用上函数索引。

    引申联想：写完该小节，大家明白了使用自定义函数一个很关键的一点，就是如果要用到自定义函数的函数索引，必须要有DETERMINISTIC的关键字，不过这个 DETERMINISTIC到底是什么东西啊，为什么自定义函数的函数索引能否建成功完全依赖这个关键字，这么神奇！原来对于指定了DETERMINISTIC的函数，在一次调用中，对于相同的输入，只进行一次调用。这要求函数的创建者来保证DETERMINISTIC的正确性，如果这个函数的返回值和输入参数没有确定性关系，会导致函数结果异常的。这里我举个例子，大家体会一下
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
建立一个带DETERMINISTIC关键字的函数，功能就是返回值为1
SQL>  CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER DETERMINISTIC
  2      AS
  3      BEGIN
  4      DBMS_LOCK.SLEEP(0.1);
  5      RETURN 1;
  6      END;
  7  /
Function created
SQL> set timing on
发现执行时间非常快，0.765秒完成
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
              1
              1
           这里略去另外73个记录（值都为1）
75 rows selected
Executed in 0.765 seconds
用不带关键字DETERMINISTIC的方式建立函数，功能也是返回值为1
SQL>  CREATE OR REPLACE FUNCTION F_DETERMINISTIC RETURN NUMBER
  2    AS
  3    BEGIN
  4    DBMS_LOCK.SLEEP(0.1);
  5   RETURN 1;
  6    END;
  7  /
Function created
Executed in 0.047 seconds
发现执行速度慢了好多，8.469秒
SQL> SELECT F_DETERMINISTIC FROM user_tables;
F_DETERMINISTIC
---------------
              1
              1
这里也略去另外73个记录（值都为1）
75 rows selected
Executed in 8.469 seconds
其实速度相差如此之大的原因是什么呢？那是因为带DETERMINISTIC的函数对于相同的输入只会运算一次，认为值是一样的，就把上次算出来的结果直接引用了，所以为什么第一种会执行速度这么快，因为第一种情况下根本该自定义函数就只调用了1次，然后另外那74次结果都是直接考虑到输入相同，直接把第 1次调用的结果拿来用而已。而第二种其实是调用了75次。我这里故意举了无输入值函数方式来做实验因为这样比较特殊，没有输入就是表示输入相同值的含义！（有输入的情况我也实验过了，一样！）为什么ORACLE要对自定义函数做这个DETERMINISTIC限制呢？我思考了一下，应该是有这两个原因吧：1、可避免我们建立一个不确定输出值的函数！比如我上一小节的函数索引建立失败（不过那不是建立自定义函数，是ORACLE自带函数失败），就和相同输入返回不同结果有关系。2、SQL中写函数函数很容易出现性能问题，比如你写个不好的函数，又被大量调用，那估计执行起来麻烦要大了，ORACLE这样做了，可以让写自定义函数的人在调用的时候一次执行多次使用结果，速度也可以快很多。这两点是我猜测，有机会找ORACLE公司的人确认一下。

[ 本帖最后由 wabjtam123 于 2009-12-29 11:36 编辑 ]

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/40011/viewspace-674038/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/40011/viewspace-674038/