DETERMINISTIC Function and Function Based Index on Deterministic Function

Exploring DETERMINISTIC Function and Function based indexes we found: 

• Deterministic functions can be created on a function having DML command on it, however you cannot refer such function from SELECT SQL.
• You can create Function Based Index on a Deterministic Function only. In other words a function must be deterministic so that Index could be created on it.
• Once you create function based index on a deterministic function,  all possible value from that function is stored in Index and function body will not be executed at all.
• Even if you have written DML in Deterministic function body that DML will execute only when you create function based index on that Deterministic Function and DML will not execute in any call to that function ever.
• Even if you re-create function with different arithmetic for return value, referring it will give same result in SQL SELECT statement as of previous function return on which Index was created .
• Deterministic function behave same as other functions when we called them in plsql code and not with a SELECT statement. Inside plsql block or Sql*Plus environment the DML body will execute even if it has index created on it.
• Table used in SELECT query in Deterministic code and Table on which Function Based Index is created using that Deterministic Function should not be same. Else we will get ORA-04091: table is mutating, trigger/function may not see it error.

1. Index creation failed on Non-Deterministic Function:

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

  2  RETURN NUMBER

  3  IS

  4  BEGIN

  5  RETURN v_value+1000;

  6  END;

  7  /

Function created.

SQL> CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal));

CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal))

                                *

ERROR at line 1:

ORA-30553: The function is not deterministic 

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

 2  RETURN NUMBER DETERMINISTIC

 3  IS

 4  BEGIN

 5  RETURN v_value+1000;

 6  END;

 7  /

SQL> CREATE INDEX deter_ind1 ON EMP2(determinst_test(sal));

Index created.

2. DML inside a Deterministic function which is referred in SELECT query is acceptable if it has index created on it else it will through ORA-14551.

SQL> drop index DETER_IND1;

Index dropped.

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

  2  RETURN NUMBER DETERMINISTIC

  3  IS

  4  BEGIN

  5   INSERT INTO DETER_INSRT

  6   VALUES (v_value, 'Value is '||v_value);

  7  RETURN v_value+1000;

  8  END;

  9  /

Function created.

SQL>  SELECT EMPNO, ENAME, sal, determinst_test(sal)

  2     FROM EMP2

  3     WHERE determinst_test(sal) >= 3000;

   WHERE determinst_test(sal) >= 3000

         *

ERROR at line 3:

ORA-14551: cannot perform a DML operation inside a query

ORA-06512: at "SCOTT.DETERMINST_TEST", line 5

SQL> CREATE INDEX  DETER_IND1 on EMP2(determinst_test(sal));

Index created.

SQL> SELECT EMPNO, ENAME, sal, determinst_test(sal)

  2    FROM EMP2

  3    WHERE determinst_test(sal) >= 3000;

     EMPNO ENAME             SAL DETERMINST_TEST(SAL)

---------- ---------- ---------- --------------------

      7698 BLAKE            2850                 3850

      7566 JONES            2975                 3975

      7788 SCOTT            3000                 4000

      7902 FORD             3000                 4000

3. Change in function return arithmetic will not give different result in SELECT SQL, however Sql*Plus call will make it behave as normal function call.

SQL> CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

  2  RETURN NUMBER DETERMINISTIC

  3  IS

  4  BEGIN

  5  RETURN v_value+5000;

  6  END;

  7  /

Function created.

SQL> SELECT EMPNO, ENAME, sal, determinst_test(sal)

  2    FROM EMP2

  3    WHERE determinst_test(sal) >= 3000;

     EMPNO ENAME             SAL DETERMINST_TEST(SAL)

---------- ---------- ---------- --------------------

      7698 BLAKE            2850                 3850

      7566 JONES            2975                 3975

      7788 SCOTT            3000                 4000

      7902 FORD             3000                 4000

SQL> EXEC :v_deter := determinst_test(300);

PL/SQL procedure successfully completed.

SQL> print v_deter;

   V_DETER

----------

      5300

4. DML inside Deterministic function is executed either when we create index on it or when we call it from plsql or sql*plus Exec command. DML is not executed when it is referred in SELECT.

SQL> SELECT * FROM DETER_INSRT;

no rows selected

SQL> CREATE INDEX  DETER_IND1 on EMP2(determinst_test(sal));

Index created.

SQL> SELECT * FROM DETER_INSRT;

        C1 C2

---------- --------------------

       300 Value is 300

       800 Value is 800

      1600 Value is 1600

      1250 Value is 1250

      2975 Value is 2975

      2850 Value is 2850

      3000 Value is 3000

      1500 Value is 1500

      1100 Value is 1100

       950 Value is 950

However DML executes if we call this function not from SELECT SQL.

SQL> EXEC :VAL := determinst_test(1000);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM DETER_INSRT;

        C1 C2

---------- --------------------

       300 Value is 300

       800 Value is 800

      1600 Value is 1600

      1250 Value is 1250

      2975 Value is 2975

      2850 Value is 2850

      3000 Value is 3000

      1500 Value is 1500

      1100 Value is 1100

       950 Value is 950

      2000 Value is 2000

5. It has been observed that if we create a deterministic function and then a function based index using that DETERMINISTIC function, we cannot update values of table on which it has been referred. 
Conclusion: Table used in SELECT query in Deterministic code and Table on which Function Based Index is created using that Deterministic Function should not be same. 

We created following DETERMINISTIC function:

 CREATE OR REPLACE FUNCTION determinst_test(v_value NUMBER)

  RETURN NUMBER  DETERMINISTIC

  IS

 v_sal NUMBER;

 BEGIN

 SELECT SAL into v_sal FROM emp2 where empno=v_value;

  RETURN v_value+1000+v_sal;

  END;

 /

Then Index on table, using this deterministic function:

SQL>  CREATE INDEX  DETER_IND1 on EMP2(determinst_test(sal));

Index created.

Then we tried to update value in this table:

SQL> update emp2 set sal=sal+10000 where empno=7876;

update emp2 set sal=sal+10000 where empno=7876

*

ERROR at line 1:

ORA-04091: table SCOTT.EMP2 is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.DETERMINST_TEST", line 6

Solution: If we drop the index or create the index on some different table then we can update the record.