Skip to content

G-7460: Try to define your packaged/standalone function deterministic if appropriate.

Major

Efficiency

Reason

A deterministic function (always return same result for identical parameters) which is defined to be deterministic will be executed once per different parameter within a SQL statement whereas if the function is not defined to be deterministic it is executed once per result row.

Example (bad)

1
2
3
4
5
create or replace package department_api is
   function name_by_id(in_department_id in departments.department_id%type)
      return departments.department_name%type;
end department_api;
/

Example (good)

1
2
3
4
5
6
create or replace package department_api is
   function name_by_id(in_department_id in departments.department_id%type)
      return departments.department_name%type
      deterministic;
end department_api;
/