When PL/SQL functions are being called in multi-table joins, it is very important to keep in mind that you are operating on the merged sets. The following is a basic example:
Note the outer join between EMP and DEPT. This causes the query to return 15 rows: 14 rows represent employees with associated departments; the 15th row represents a department that does not have any employees. The function F_CHANGE_NR is also fired 15 times because it is being applied after the join. As a result, 1 out of 15 calls is unnecessary. This does not seem like much, but it illustrates the point that joins change the number of function calls.
The same 15 executions will occur even if you pass a column from the DEPT table into the function. This leads to even worse overhead because you have only four distinct departments. Anything with more than four calls is a waste of resources (11 extra calls!):
The last example illustrates the most common issue with using PL/SQL functions inside of SQL. If developers pass a column from the small table used in the join, they expect the total number of calls to this function to be relatively small. This is a mistake; however, there are special techniques to make Oracle aware that the total number of calls could be decreased. The majority of these techniques have to do with caching. In addition, there is a special clause, DETERMINISTIC, that tells Oracle to assume that the function will always bring the same return value for the same IN argument. Be careful, however, because that clause is not a directive and Oracle could ignore it. But, more often than not, it helps:
This time, the DETERMINISTIC clause worked as designed and dropped the total number of function calls to F_CHANGE_NR to four to match the number of distinct departments.
Leave a Reply