How To Use Deterministic Clause In Oracle

How To Use Deterministic Clause In Oracle

October 16, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online tutorial is based on examples to make it easier to follow. Oracle Deterministic clause is used in various place and to explain how does it work we will start with an Oracle function. This clause will help to gain better performance and reduce the amount of executions when the function has been called out many times using the exact same parameter. The Oracle Deterministic clause will keep the first execution result in its memory and returns it without calling the function out any more.

To show how works the Deterministic clause in Oracle we need to create Oracle function without the Deterministic clause and then another one with it. In the Oracle function we will declare an Id parameter to give it to the function and then the function returns a customer name. Every time when the Oracle function gets executed it sends a message into output and that message shows how many times we did execute it. The function name is get_customer_name and the input parameter is p_customer_id as a NUMBER type.

CREATE FUNCTION get_customer_name (p_customer_id NUMBER)
 RETURN VARCHAR2
IS
v_name VARCHAR2 (500) ;
BEGIN
 SELECT cus.name
   INTO v_name
   FROM (SELECT 1 AS id,'Emma' AS name
           FROM dual
         UNION ALL
         SELECT 2 AS id,'John' AS name
           FROM dual ) cus
  WHERE cus.id = p_customer_id;

  dbms_output.put_line ('Returned name=>'||v_name) ;

  RETURN v_name;

END get_customer_name;

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This Oracle function has been created in database to show how does work the functionality with the usual one. As you see we are using in this example Oracle dual tables and the Oracle SQL query returns for the 1st Id name “Emma” and for 2nd Id name “John“. Under the Oracle Select Into statement we have build-in procedure DBMS_OUTPUT.PUT_LINE that sends the message on the screen.

Now we will call out the function in Oracle Select statement as on the following Oracle DBA example:

 SELECT get_customer_name(cus.ID) AS name
   FROM
    (SELECT 1 AS ID
       FROM dual
        CONNECT BY rownum < 11
    ) cus ;

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

As you see from the output above the get_customer_name function was called out 10 times and the same amount of executions is showing on the DBMS_OUTPUT screen too. In short, the function was executed the same amount of times as the SQL query has called it out.

Lets try the same condition with using Oracle Deterministic clause. Do to so we need to amend the Oracle function code and add the DETERMINISTIC clause into it. Take a look at the following example:

CREATE FUNCTION get_customer_name (p_customer_id NUMBER)
 RETURN VARCHAR2 DETERMINISTIC
IS
v_name VARCHAR2 (500) ;
BEGIN
 SELECT cus.name
   INTO v_name
   FROM (SELECT 1 AS id,'Emma' AS name
           FROM dual
         UNION ALL
         SELECT 2 AS id,'John' AS name
           FROM dual ) cus
  WHERE cus.id = p_customer_id;

  dbms_output.put_line ('Returned name=>'||v_name) ;

  RETURN v_name;

END get_customer_name;

To deploy this code into the Oracle database we will need to drop the function. For your information if you created the same function using Create and Replace statement then you do not have to use the following drop command, but for safety reasons we are going to drop it and create the same function with the code above.

DROP FUNCTION get_customer_name;

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

When the function is deleted from your database the message on your screen should look as on the output above. Now take the next code with the Oracle DETERMINISTIC clause above and execute it. As it done on the following picture.

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

After this last steps we have in the Oracle database an Oracle function with the same functionality as the first one, but with an extra thing – it has in it the Oracle DETERMINISTIC clause after the “RETURN VARCHAR2” keywords. Lets try this function with the same Oracle Select query as we did use before. You should see the same result as on the output below.

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

You can see on the picture above that we did execute the SQL query that returned 10 lines. All 10 “Emma” names are in the script output window. Please take a look at DBMS_OUTPUT windows you see the difference with the first Oracle function and with this in execution times – only 1 time. As we did explain above the DETERMINISTIC clause in Oracle database keeps the results and parameters in its memory. The function was executed on the 1st call because it didn’t know the result, but for the following 9 times it did have the result and it returned it from memory and without calling out the Oracle function. This result has been kept in memory for only 1 SQL call and on the following execution the function will be executed again on the 1st line. WHY SO? Oracle assumes that 1 SQL call takes a short time and during this time nobody changes the data or in this case the name.

Important TIPS:
The Oracle Deterministic clause works only inside the Oracle Select and the data in memory is kept until the select is finished – no longer.

To prove what we just said lets try the same get_customer_name with the Oracle DETERMINISTIC clause in PL/SQL loop:

DECLARE
  v_name VARCHAR2(500);
  v_id PLS_INTEGER := 1;
BEGIN
  FOR i IN 1..10
  LOOP
    v_name := get_customer_name(v_id);
  END LOOP;
END;

dba oracle dba database management system oracle performance tuning oracle deterministic oracle deterministic in oracle the stock market plumbing personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In this example as on all previous ones we are going to call out the Oracle function 10 times and the DBMS_OUTPUT window shows us how many times the function has been executed. Yes, all 10 times and none of them came from its memory. The Oracle DETERMINISTIC clause works only inside the SQL transaction.

See Also:

Leave a Reply

Your email address will not be published. Required fields are marked *

*