* Does the 'deterministic' key word in a function have any impact on how the optimizer
  handle the query? Not in the following example. Function was executed same number of
  times for both deterministic and non-deterministic function.



SQL>  select * from tyu;

        C1
----------
         0
         1
         2
         3
         4
         5
         6
         7
         8
         9

10 rows selected.

SQL> 
SQL> create or replace function func2_(c1 number) return number
  2    deterministic
  3    as
  4    x number;
  5    begin
  6    dbms_application_info.read_client_info(x);
  7    dbms_application_info.set_client_info(x+1);
  8    return c1+10;
  9    end;
 10  /

Function created.

SQL> create or replace function func3_(c1 number) return number
  2  as
  3  x number;
  4  begin
  5  dbms_application_info.read_client_info(x);
  6  dbms_application_info.set_client_info(x+1);
  7  return c1+x;
  8  end;
  9  /

Function created.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- equality query with deterministics function
SQL> select func2_(c1) from tyu where func2_(c1)=10;

FUNC2_(C1)
----------
        10

SQL> set serveroutput on
SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
11

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- range query with deterministics function
SQL> select func2_(c1) from tyu where func2_(c1)>0;

FUNC2_(C1)
----------
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19

10 rows selected.

SQL> set serveroutput on
SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
20

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- equality query with non-deterministics function
SQL> select func2_(c1) from tyu where func2_(c1)=10;

FUNC2_(C1)
----------
        10

SQL> set serveroutput on
SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
11

PL/SQL procedure successfully completed.

SQL> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

SQL> 
SQL> -- range query with non-deterministics function
SQL> select func2_(c1) from tyu where func2_(c1)>0;

FUNC2_(C1)
----------
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19

10 rows selected.

SQL> set serveroutput on
SQL> declare
  2  x number;
  3  begin
  4  dbms_application_info.read_client_info(x);
  5  dbms_output.put_line(x);
  6  end;
  7  /
20

PL/SQL procedure successfully completed.

SQL>