基于函数索引生效的条件

今天看了Oracle 10g Concepts中基于函数索引，对比9i，10g中引入了skip_unusable_indexes，可以跳过unusable的索引。从概念上看其他没有什么区别


对于基于函数的索引，要使它起作用，必须注意以下几点。
1.基于函数的索引只能用于CBO，不能在RBO中起作用
2.基于函数的索引的表在使用dbms_stats收集统计信息后，索引才能起作用
3.自定义函数用于基于函数的索引，在创建自定义函数时必须使用DETERMINISTIC关键字
4.基于函数的索引必须要有EXECUTE的权限来运行自定义函数，否则不仅INDEX不起作用，而且表查询都会出错。10g中可以把index状态改为unusable，并且skip_unusable_indexes来跳过这个索引


下面将通过实验的方式来详细说明

1.基于函数的索引只能用于CBO，不能在RBO中起作用

SQL> create table t ( a int, b char);

Table created.

SQL> begin
  2     for i in 1 .. 10000 loop
  3     insert into t values(i,'a');
  4     end loop;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> create index t_i1 on t(a - 10);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed.

SQL> set autotrace on exp
SQL>  select * from t where (a - 10) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1977792910

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("A"-10=100)


看上面的执行计划，使用了函数索引T_I1，下面使用hint来走RBO


SQL> select /*+ RULE */ * from t where (a - 10) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------

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

   1 - filter("A"-10=100)

Note
-----
   - rule based optimizer used (consider using cbo)

使用了RBO，执行计划不走函数索引，使用全表扫描





2.基于函数的索引的表在使用dbms_stats收集统计信息后，索引才能起作用

删除T表上的统计信息

SQL> exec dbms_stats.delete_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> select * from t where (a - 10) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|*  1 |  TABLE ACCESS FULL| T    |
----------------------------------

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

   1 - filter("A"-10=100)

可以看到，在没有表统计信息的情况下，基于函数的索引是不起作用的



3.自定义函数用于基于函数的索引，在创建自定义函数时必须使用DETERMINISTIC关键字

创建自定义函数，不使用DETERMINISTIC

SQL> create or replace function myf1(i_a int)
  2  return int
  3  is
  4  begin
  5     return(i_a - 10);
  6  end;
  7  /

Function created.

SQL> drop index t_i1;

Index dropped.

SQL> create index t_i1 on t(myf1(a));
create index t_i1 on t(myf1(a))
                       *
ERROR at line 1:
ORA-30553: The function is not deterministic


可以看到在创建自定义函数的基于函数索引时会报ORA-30553错误，下面我们加上DETERMINISTIC关键字


SQL> create or replace function myf1(i_a int)
  2  return int DETERMINISTIC
  3  is
  4  begin
  5     return(i_a - 10);
  6  end;
  7  /

Function created.

SQL> create index t_i1 on t(myf1(a));

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed.

SQL> select * from t where myf1(a) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1977792910

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("JASON"."MYF1"("A")=100)

加上DETERMINISTIC关键字后，自定义函数的基于函数的索引创建成功并起作用




4.基于函数的索引必须要有EXECUTE的权限来运行自定义函数，否则不仅INDEX不起作用，而且表查询都会出错。10g中可以把index状态改为unusable，并且skip_unusable_indexes来跳过这个索引

我先在另一个用户CBO下创建一个myf1自定义函数，再把execute的权限赋于jason

SQL> conn cbo/cbo
Connected.
SQL> create or replace function myf1(i_a int)
  2  return int DETERMINISTIC
  3  is
  4  begin
  5     return(i_a - 10);
  6  end;
  7  /

Function created.

SQL> grant execute on myf1 to jason;

Grant succeeded.


在jason下创建cbo.myf1自定义函数的索引

SQL> conn jason/jason
Connected.
SQL> drop index t_i1;

Index dropped.

SQL> select * from t where myf1(a) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   600 |     9  (23)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   100 |   600 |     9  (23)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("MYF1"("A")=100)

SQL> create index t_i1 on t(cbo.myf1(a));

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed.

SQL> select * from t where cbo.myf1(a) = 100;

         A B
---------- -
       110 a


Execution Plan
----------------------------------------------------------
Plan hash value: 1977792910

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     6 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     6 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_I1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access("CBO"."MYF1"("A")=100)

从上面的执行计划看出，使用了自定义函数的函数索引，下面将把jason在cbo.myf1的execute权限收回

SQL> conn cbo/cbo
Connected.
SQL> revoke execute on myf1 from jason;

Revoke succeeded.

SQL> conn jason/jason
Connected.
SQL> set autotrace on exp
SQL> select * from t where cbo.myf1(a) = 100;
select * from t where cbo.myf1(a) = 100
      *
ERROR at line 1:
ORA-00904: : invalid identifier

SQL> select * from t;
select * from t
      *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> alter index t_i1 unusable;

Index altered.

SQL> select * from t where rownum = 1;
select * from t where rownum = 1
      *
ERROR at line 1:
ORA-00904: : invalid identifier


把EXECUTE权限收回后，再执行select时会报ORA-00904错误，把T_I1状态改为unusable，还是报ORA-00904错误，这点和书上有些不同，希望高手解答一下

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

转载于:http://blog.itpub.net/8745319/viewspace-573164/