Optimizing a PL/SQL Function for Always Return the Same Result

PL/SQL Function for Always Return the Same ResultProblem

You want to create a PL/SQL function that will return the same result whenever a given input, or set of inputs, is presented to it. You want the database to optimize based upon that deterministic nature.

 

Solution

Specify the DETERMINISTIC keyword when creating the function to indicate that the function will always return the same result for a given input. For instance, you want to return a specific manager name based upon a given manager ID. Furthermore, you want to optimize for the fact that any given input will always return the same result. The following example demonstrates a function that does so by specifying the DETERMINISTIC keyword:

CREATE OR REPLACE FUNCTION manager_name(mgr_id IN NUMBER)

   RETURN VARCHAR2

DETERMINISTIC IS

  first_name     employees.first_name%TYPE;
  last_name      employees.last_name%TYPE;

BEGIN

  IF mgr_id IS NOT NULL THEN

    SELECT first_name, last_name
      INTO first_name, last_name
      FROM EMPLOYEES
    WHERE employee_id = mgr_id;

    RETURN first_name || ' ' || last_name;

  ELSE

    RETURN 'N/A';

  END IF;

EXCEPTION

  WHEN NO_DATA_FOUND THEN
    RETURN 'N/A';

END;

 

This function will return the manager name for a matching EMPLOYEE_ID. If there are no matches for the EMPLOYEE_ID found, then N/A will be returned.

 

How It Works

A deterministic function is one that always returns the same resulting value as long as the parameters that are passed in are the same. This type of function can be useful for improving performance. The function will be executed only once for any given set of parameters. This means that if the same parameters are passed to this function in subsequent calls, then the function will be bypassed and return the cached value from the last execution using those parameters. This can really help in cases where calculations are being performed and repeated calls to the function may take a toll on performance.

The DETERMINISTIC clause is required in a couple of cases. In the event that you are calling a function in an expression of a function-based index, you need to write the function as DETERMINISTIC, or you will receive errors. Similarly, a function must be made DETERMINISTIC if it is being called in an expression of a materialized view query or if the view is marked as ENABLE QUERY REWRITE or REFRESH FAST.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 388 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 156 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 383 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 389 views Гвен Sun, 03 Jun 2018, 11:54:40

Comments on Optimizing a PL/SQL Function for Always Return the Same Result

ildergun
ildergun Working recipe. Thanks to the blogger! 1 year ago
loader
loader
Attachment
dbstalker
dbstalker Nice solution! Thank you!) 1 month ago
loader
loader
Attachment
Please login to comment