函数索引--DETERMINISTIC

函数索引--DETERMINISTIC

To create a function-based index, in addition to the prerequisites for creating a
conventional index, if the index is based on user-defined functions, then those
functions must be marked DETERMINISTIC. Also, you must have the EXECUTE object
See Also:
■ Oracle Database Concepts for a discussion of indexes
■ ALTER INDEX on page 10-79 and DROP INDEX on page 17-50
CREATE INDEX
SQL Statements: CREATE CLUSTER to CREATE JAVA 14-61
privilege on any user-defined function(s) used in the function-based index if those
functions are owned by another user.


qn@RAC> create table  t_deterministic ( a int , b int ) ;
Table created.

qn@RAC> insert into  t_deterministic select rownum ,rownum/2 from dual connect by rownum < 1000 ;
999 rows created.


create or replace function func_deterministic(a_len t_deterministic.a%type )
  return number deterministic is   v_len number;
  begin
  select length(a) into v_len from t_deterministic where a=a_len;
  return v_len;
  6    end;
  7  /
 
 
qn@RAC> create index  idx_t_deterministic on t_deterministic(func_deterministic(a)) ;
Index created.

qn@RAC> set autot traceonly
qn@RAC> select * from t_deterministic where func_deterministic(a) = 2
  2  /
90 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1476891661
---------------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |    90 |  3510 |    2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_DETERMINISTIC      |    90 |  3510 |    2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN        | IDX_T_DETERMINISTIC |    90 |      |    1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("QN"."FUNC_DETERMINISTIC"("A")=2)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
       1081  recursive calls
      0  db block gets
       7042  consistent gets
      1  physical reads
      0  redo size
       2393  bytes sent via SQL*Net to client
    574  bytes received via SQL*Net from client
      7  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
     90  rows processed





http://blog.itpub.net/9240380/viewspace-743358/

 3,加与不加它的区别
          4,哪些语句可以有DETERMINISTIC，此处指FUNCTION,PACKAGE,PACKAGE BODY,PROCEDURE,VIEW
                1,create procedure过程中不能有DETERMINISTIC
                2,CRREATE OR REPLACE PACKAGE的语法参考：Oracle? Database PL/SQL User's Guide and Reference
                     测试包规范
                        --经测试可在包规范中创建有DETERMINISTIC的函数声明
                       CREATE OR REPLACE PACKAGE PKG_DETERMINISTIC
                       AS
                        FUNCTION FUNC_TEST
                         RETURN NUMBER DETERMINISTIC;
                        
                       END;
                      
                       --续上测试，包体也可以创建有DETERMINISTIC的函数声明
                       CREATE OR REPLACE PACKAGE BODY PKG_DETERMINISTIC
                       IS
                       function FUNC_TEST
                       return number deterministic is
                       v_len number;
                        begin
                       select length(a) into v_len from t_deterministic;
                        return v_len;
                       end;
                       END PKG_DETERMINISTIC;