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)

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)

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;
/