Determining Deterministic I – Introduction

There have been a couple of threads recently on the Oracle forums about deterministic functions and the performance benefits thereof.

These keep nagging me that I have been meaning to write something up about this DETERMINISTIC assertion and to also investigate a couple of aspects of this further.

Put simply, the DETERMINISTIC keyword asserts that a function when called with a certain input will always return the same output.

By the way, although you might declare your function as DETERMINISTIC, Oracle does not verify this assertion.

A brief history

The DETERMINISTIC keyword in the CREATE FUNCTION statement appeared in Oracle 8i.

Here’s what the Oracle 8.1.7 documentation said about the DETERMINISTIC assertion:

DETERMINISTIC is an optimization hint that allows the system to use a saved copy of the function’s return result (if such a copy is available). The saved copy could come from a materialized view, a function-based index, or a redundant call to the same function in the same SQL statement. The query optimizer can choose whether to use the saved copy or re-call the function.

From what I can remember, the first such optimisations were not available until about 10.2.

Nobody Move – FBI

So, prior to any of these optimisations, the main purpose of the DETERMINISTIC keyword was to enforce that any function used in a function-based index was declared DETERMINISTIC (although not necessarily implemented as such as mentioned above – an attempt otherwise would result in an error ORA-30553: The function is not deterministic.

And just to prove it:

SQL> create or replace function f1 (
  2    i1 in number
  3  )
  4  return number
  5  as
  6  begin
  7   return i1;
  8  end;
  9  /

Function created.

SQL> create table det1
  2  (id          number
  3  ,val1        number
  4  ,padded_val1 varchar2(128)
  5  ,constraint pk_det1 primary key(id));

Table created.

SQL> insert into det1
  2  select rownum
  3  ,      mod(rownum, 10)
  4  ,      rpad(mod(rownum, 10),128)
  5  from   dual
  6  connect by rownum <= 10000;

10000 rows created.

SQL> create index i_det1_f1_id on det1(f1(id));
create index i_det1_f1_id on det1(f1(id))
                                  *
ERROR at line 1:
ORA-30553: The function is not deterministic


SQL>                             

If we address this oversight, it should all be fine:

SQL> create or replace function f1 (
  2    i1 in number
  3  )
  4  return number
  5  deterministic
  6  as
  7  begin
  8   return i1;
  9  end;
 10  /

Function created.

SQL> create index i_det1_f1_id on det1(f1(id));

Index created.

SQL> select index_name, table_name, index_type, status, funcidx_status
  2  from   dba_indexes
  3  where  index_name = 'I_DET1_F1_ID';

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE                  STATUS   FUNCIDX_
------------------------------ ------------------------------ --------------------------- -------- -
I_DET1_F1_ID                   DET1                           FUNCTION-BASED NORMAL       VALID    ENABLED

SQL> 

Just for completeness – and this is really function-based index 101 rather than determinism – what happens if we change the function after the index has been created?

… And here is where I get hit by a distraction. I was trying to restrict all my examples in this series to 11gR2 for simplicity (and because of the environments I have available currently).

On 11.2.0.2:

SQL> create or replace function f1 (
  2    i1 in number
  3  )
  4  return number
  5  --deterministic
  6  as
  7  begin
  8   return i1;
  9  end;
 10  /

Function created.

SQL> select index_name, table_name, index_type, status, funcidx_status
  2  from   dba_indexes
  3  where  index_name = 'I_DET1_F1_ID';

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE                  STATUS   FUNCIDX_
------------------------------ ------------------------------ --------------------------- -------- -
I_DET1_F1_ID                   DET1                           FUNCTION-BASED NORMAL       VALID    ENABLED

SQL> 

That’s not quite what I expected to see in the column FUNCIDX_STATUS but it’s been a long time since I’ve deliberately messed around with FBIs.

If you were to do this in 9i, then as this output from 9.2.0.8 shows:

SQL> select index_name, table_name, index_type, status, funcidx_status
  2  from   dba_indexes
  3  where  index_name = 'I_DET1_ID';

INDEX_NAME                     TABLE_NAME                     INDEX_TYPE                  STATUS   FUNCIDX_
------------------------------ ------------------------------ --------------------------- -------- -
I_DET1_F1_ID                   DET1                           FUNCTION-BASED NORMAL       VALID    DISABLED

SQL> 

So, not a lot happens in 11gR2

I’m 90% sure that the behaviour in 11.1.0.6 was the same as 9.2.0.8 – FUNCIDX_STATUS gets set to DISABLED – because I’ve previously built release scripts based on that status column which worked in 11.1 but I’ve not got access to such an environment to compare right now.

I was thinking bug perhaps but perhaps this quote from the 11gR2 documentation justifies this behaviour:

If you change the semantics of a DETERMINISTIC function and recompile it, then existing function-based indexes and materialized views report results for the prior version of the function. Thus, if you change the semantics of a function, you must manually rebuild any dependent function-based indexes and materialized views.

I’m not sure – but it’s a change certainly.

Next part – some benefits of determinism.