Oracle Performance and Engineering

Remember the post 'PL/SQL function in where clause'? We had a certain PL/SQL function that always returns the same value (exactly like a constant) equated with a column in WHERE clause. Though the function was merely serving as a constant, Oracle actually evaluated the function for every row returned by the dataset! Oracle was *not* smart enough to execute the function just once, figure out that the returned value for the function is not going to change within the context of this query (in this case, it was NEVER gonna change!) - and then use that constant throughout the query execution.

Well, Oracle *used to* behave the same way within PL/SQL too. A codepiece like -

<.....
LOOP
a *b *c = d;
-- say, d will remain a constant throughout the LOOP
....

END LOOP;
.....>

would, so far, have executed (a * b * c) as many times as the LOOP will be iterated through. So earlier you would see statement like this all over the 'PL.SQL best practice guide' -

" Don’t write an expression inside a loop if it remains constant over all iterations of the loop.”

The case is not true anymore with 10g.

10g PL/SQL compiler is smart enough to figure out that "d" is indeed a constant - and would NOT compute the value for D once per iteration. Oracle 10g would evaluate it just once per loop.

Say the following code -

declare
a number := 10;
b number := 23892;
c number := 42784782;
d number;
e number;
begin
execute immediate 'alter session set sql_trace = true';
for i in 1 .. 1000000 loop
d := a * b * c;
e := (d * 1001);
end loop;
end;


Took 2.64 secs to run on 9i. (a * b * c is evaluated 1 million times)

Took 0.03 secs to run on 10g. (a *b * c is evaluated only ONCE).

However, on my tests against 10g - SQL is still not smart enough to evaluate this "constant" just once per query if the constant is supplied in the WHERE clause in form of a PL/SQL function.