基于函数索引生效的条件

今天看了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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值