Oracle之利用函数索引减少递归调用

drop table t1 purge;
drop table t2 purge;

create table t1 (first_name varchar2(200),last_name varchar2(200),id number);                    
create table t2 as select * from dba_objects where rownum<=1000;                                 
insert into t1 (first_name,last_name,id) select object_name,object_type,rownum from dba_objects where rownum<=1000;                                    

commit;                                                                               

create or replace function get_obj_name(p_id t2.object_id%type) return t2.object_name%type DETERMINISTIC is
v_name t2.object_name%type;
begin
select object_name
into v_name
from t2
where object_id=p_id;
return v_name;
end;
/     

                                     
set linesize 1000
set autotrace traceonly   
select *  
from t1 where get_obj_name(id)='TEST'  ;
执行计划
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |  2170 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |  2170 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
       1057  recursive calls
          0  db block gets
      16007  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


create index idx_func_id on t1(get_obj_name(id));


执行计划
----------------------------------------------------------
Plan hash value: 4083325411


-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |    10 | 22190 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1          |    10 | 22190 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_FUNC_ID |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        410  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)

          0  rows processed

解析：建函数索引以后，通过执行计划发现，1057  recursive calls递归调用没有了，

由于函数索引特性会预计算所以递归调用没有了。