函数索引

1. 无法用上函数索引

具体举例来说明

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. 函数索引建立失败
现构造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

这点大家应该不难明白吧,该函数充满了不确定性!这就是函数索引无法创建的原因,函数索引是预先在索引中存储了函数索引的结果以备使用,现在这个函数值不确定了,怎么存储函数结果?

总结:通过本小节的学习,大家应该能加深了对函数索引的认识吧,建函数索引的函数必须要有确定性,返回不确定结果的函数就无法建立起来,因为不确定结果根本不能被函数索引预存储,所以要失败!我就曾经要建立一个这样不确定函数的函数索引而失败,最后选择了其它方式!

3. ORA-30553错误

我们平时建的函数索引一般都是对ORACLE的自带函数做函数索引,如upper()等等,但是如果我们要进行基于自定义函数的索引的建立的时候,必须使用DETERMINISTIC关键字,否则会报ORA-30553错误,这点要引起大家的注意。

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公司的人确认一下。

Trackback

no comment untill now

Add your comment now

切换到手机版