This tutorial is based on examples to be easier to follow. Here we are trying to go through the very basics about using your Oracle function in the SQL query and what to avoid that makes your SQL query slow.
To go through the examples below we’ll need a SQL query and to keep it simple we will use the following DUAL table and CONNECT BY operator will return 10 lines. Take a look at the output below.
SELECT rownum AS ID FROM dual CONNECT BY rownum < 11;
The SQL query needs a function to run with it and the following code will create function get_my_dynamic_id. This function will return number “3” without any special reason but that way we’ll be able to use it in where conditions. In addition, the function sends text “Executed get_my_dynamic_id” to dbms_output so we could see how many times it has been executed.
CREATE FUNCTION get_my_dynamic_id RETURN NUMBER IS BEGIN -- Send a text to DBMS_OUTPUT DBMS_OUTPUT.PUT_LINE('Executed get_my_dynamic_id'); -- Returns number 3 RETURN 3; END get_my_dynamic_id; /
The first query is for testing the function. Since the SQL query returned 10 lines the function will show 10 lines in dbms_output and that you can see at the output below.
SELECT t.ID, get_my_dynamic_id FROM ( SELECT rownum ID FROM dual CONNECT BY rownum < 11) t;
Now the fourth query is using the get_my_dynamic_id function in the WHERE clause. The function returns only number 3 so the SQL will return on one line (ID = 3) but the function has been executed 10 times as shows the Dbms_output.
SELECT t.ID FROM ( SELECT rownum ID FROM dual CONNECT BY rownum < 11) t WHERE t.ID = get_my_dynamic_id;
The example above shows that every function we are using in the WHERE and who is not DETERMINISTIC will be executed as many times as the query returns without the function. When your function does more than returns number this SQL query may become very slow. The following query does the same as the last one only with changing the structure the function will be executed only 1 time.
SELECT t.ID FROM ( SELECT rownum ID FROM dual CONNECT BY rownum < 11) t WHERE t.ID IN (SELECT get_my_dynamic_id FROM dual);
Using Nested Loop Join with the Dual table and the function in it reduced amount of execution because of the nature of this join. The query used with “IN” will be executed first and then runs the main query.
The next query is another example how programmers may try reduce the amount of executions. This query has the function and the Dual table declared in the FROM part as another table to join with. Only as the output shows the execution times is up to 10 again, so there is not much help in this way of writing query.
SELECT t.ID FROM ( SELECT rownum ID FROM dual CONNECT BY rownum < 11) t, (SELECT get_my_dynamic_id AS ID FROM dual) d WHERE t.ID = d.ID;
There are more and different ways to reduce your function executions but this page should give you some ideas where does go the performance sometimes and how to check if this is the case.