This Online Tech Support page is based on examples to be easier to follow. Oracle Deterministic clause is used in various place and we will start with Oracle Functions. 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 same parameter and to be expected to have returned the same result. The Deterministic clause will keep the result in its memory and returns the value without executing the function again.
To show how works the Deterministic in Oracle we need to create first a usual Oracle function and then the Deterministic version of it. In this function below has declared an Id parameter to enter it to the function and then it returns a customer name. Every time when the Oracle function gets executed it send a message with a name in output and that name shows how many times the function really got execute. Take a look at the code below the function name is get_customer_name and the input parameter is p_customer_id as the 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;
This usual function has been created in Oracle database to show how does work the functionality with a normal (not Deterministing) function. When you looking at the code above you see that we are using for Oracle dual tables and the Oracle SQL query returns for the 1 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 send the message with the name into screen.
For next we will call out the function in Oracle Select statement as you see on the following example:
SELECT get_customer_name(cus.ID) AS name
FROM
(SELECT 1 AS ID
FROM dual
CONNECT BY rownum < 11
) cus ;
As you see from the picture above the get_customer_name function was called out 1o times and the same amount of executions is showing the DBMS_OUTPUT screen too. In short, the function was executed the same amount of times as the SQL query has called out.
Let’s try the same condition with using Oracle Deterministic clause, do to so we need to amend the Oracle function code and add the clause DETERMINISTIC into it. See the following code:
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 statements 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;
You should see the same message as on the picture above. Now take the code with the Oracle DETERMINISTIC clause above and execute it to save into your Oracle database as on the following picture.
After these last steps we have in our Oracle database an Oracle function with the same functionality as the first one, but with an extra thing – it has the Oracle DETERMINISTIC clause after the “RETURN VARCHAR2” keywords. Let’s try this function with the same Oracle Select query as we did use before. You should see the same result as on the Online Tech Support picture above.
You can see on the picture above that we did execute the SQL query that returned 10 lines and all 10 “Emma” names are in the script output window. Please take a look at DBMS_OUTPUT windows you see the difference with the usual Oracle function and this in execution times – only 1 time. As we did explain above the clause DETERMINISTIC in Oracle database keeps in results and parameters in 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 let’s 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;
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 Oracle database did not take from its memory. The Oracle DETERMINISTIC clause works only inside the SQL transaction.
See Also:
Home Oracle Select