pl/sql function called how many times?

Question

Assume you have the following update:

Update table set col1 = func(col2)
where col1<>func(col2)

The func function is evaluated two times for every row, or once for every row?

Thanks,

Cocoon

I need the Oracle 9i behavior... – Cocoon

#3657502 does this help? – Hyperactive

nope, the right answer is bad there... it is not the same function in select and where. – Cocoon

Answer 1

This is the kind of situation where some experimentation is useful (this was conducted on 10g). Using the following query, we can tell that normal functions, using the same parameters (in this case, none) will be executed each time they are called:

select dbms_random.value() from all_tables

This is because Oracle assumes that a function will not return the same value consistently unless you tell it otherwise. We can do that by creating a function using the deterministic keyword:

CREATE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   RETURN DBMS_RANDOM.VALUE ();
END;

Using this function instead of dbms_random in the first query tells us that the query is being executed only once, despite the many calls. But this only clarifies the select section. What if we use the same deterministic function in both a select and a where clause. We can test that using the following query:

SELECT rand_det
FROM   all_tables
WHERE  rand_det > .5;

You may have to run this several times to see our proof, but, eventually, you'll see a list of values less than 0.5. This provides us with evidence that even the deterministic function is being executed twice: once for each section it appears in. As an alternative, you can modify our deterministic function as follows, then run the subsequent query, which will reveal 2 lines written to DBMS_OUTPUT.

CREATE OR REPLACE FUNCTION rand_det
   RETURN NUMBER
   DETERMINISTIC AS
BEGIN
   DBMS_OUTPUT.put_line ('Called!');
   RETURN DBMS_RANDOM.VALUE ();
END;

SELECT rand_det
FROM   all_tables;

Muncey

+1 for the experimental evidence. What version of oracle was this? – Cruciferous

Answer 2

While I like Allan's answer for showing how to investigate this, I think the real lesson to take away is that you shouldn't rely on the answer to this question if you can avoid it.

Here's a useful post on the topic from Tom Kyte ("I've written thousands of times that you cannot rely on how many times or when or if SQL will call your function.") Even prior to 11g introducing the result cache, there are no guarantees about how many times a function will be called in processing a SQL statement. It can depend on the execution plan, which can change over time.

If your concern is performance and your function is deterministic, the 11g result cache is probably sufficient -- it won't guarantee a specific limit on the number of calls to the function, but should reduce the number of redundant calls significantly. (See link provided in @cularis answer.)

If for some reason you actually want to ensure that the two calls to the functions are distinct, I think the only way you could force that would be to add a second parameter to the function that is actually ignored but serves to prevent the result cache or optimizer from seeing the calls as identical.

Scurf

Answer 3

For 11g, it will be called once for every new occurence of col2. For the next calls, a cached result is used if caching is enabled.

Hyperactive

Wow, that's cool and above my expectations.But in 9.2? It's called once for every row or two times? – Cocoon

This is provably false. If it were universally true, this query would return the same value for all rows: select dbms_random.value() from all_tables. – Muncey

I would suspect that caching is disabled for that, also random values are generated with a seed as an input. So the input is not the same everytime. – Hyperactive

@cularis: The value function as used has no parameters, so Oracle doesn't know that there's a seed involved. The database engine isn't going to try to parse the code behind the function to determine if there's a value from an outside source involved, it's just going to look at the information available in the specification. Ultimately, this shouldn't come down to which of us thinks they know more: if you can prove that Oracle behaves as you suggest, you should do so. – Muncey

I wouldn't rely on the result cache as a guarantee that a function will be called at most once per input value during SQL processing. Cache misses happen. Also, it's worth emphasizing (although it is mentioned in the link provided) that this caching must be enabled for each function individually. – Scurf