对于基于函数的索引,要使它起作用,必须注意以下几点。
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)
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)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12712263/viewspace-589214/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12712263/viewspace-589214/