As you know, since 10g Oracle can optimize PL/SQL loops with moving code out of loops for reducing number of identical code executions.

If you have not read yet about it, I advise you to first read these two articles:

1. “PLSQL_OPTIMIZE_LEVEL: The optimization strategy of Oracle” by **Saurabh K. Gupta**

2. “PL/SQL optimisation in 10g” by **Adrian Billington**

But since 11g Oracle also can optimize code with deterministic functions too. For this to happen, code must meet the following conditions:

1. PLSQL_OPTIMIZE_LEVEL greater or equal 2

2. Parameters should not be changed in the loop body. Strictly speaking, there should not be any assignment of parameters.

3. Should not be any implicit conversions of parameters in function call, i.e. all variables/literals must be the same type as input parameters declared.

4. Should not be any call of non-deterministic functions (except some standard sql functions like to_date, to_char, nvl) or procedures in the loop

Note that this rules concerns only same scope level as this loop and not inner loops or another blocks.

For example we have code like this:

... for i in 1..100 loop n:=deterministic_function(c); end loop; ...

With enabled optimization oracle will rearrange this code to:

... for i in 1..100 loop null; end loop; n:=deterministic_function(c); ...

We can simply see it with this test case:

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as begin dbms_output.put_line(p); return p; end; / create or replace procedure p_test_deterministic as v varchar2(10); begin for i in 1..3 loop v:=xt_print_deterministic('before'); for j in 1..10 loop v:=i; v:=xt_print_deterministic('1'); end loop; v:=xt_print_deterministic('after'); end loop; end; / set serverout on; call p_test_deterministic();

After execution it printed:

before after 1 after 1 after 1

Note that:

1. ‘before’ printed once at start – it because this line was moved out from outer loop and was executed before it.

2. ‘After’ printed three times, because optimization of outer loop was stopped on inner loop.

3. ’1′ printed only three times, because it was moved out from inner cycle and added to end of outer loop.

But see what will happen if we change parameter from varchar2 literal ’1′ to number literal 1:

before after 1 1 1 1 1 1 1 1 1 1 after 1 1 1 1 1 1 1 1 1 1 after 1 1 1 1 1 1 1 1 1 1

Optimization of inner loop was disabled, because of implicit conversion, as i said above.

Now change ‘after’ to ‘after’||v:

create or replace procedure p_test_deterministic as v varchar2(10); begin for i in 1..3 loop v:=xt_print_deterministic('before'); for j in 1..10 loop v:=i; v:=xt_print_deterministic('1'); end loop; v:=xt_print_deterministic('after: '||v); end loop; end; /

Let’s see what we have got:

before 1 after: 1 1 after: 1 1 after: 1

Everything was the same, but order was changed! It is a quite easy to explain: in the previous example, the order did not matter, because it has no effect to anything. But now oracle knows that result of execution in inner loop is needed for the line after inner loop. So this line moved directly after the loop and before the remaining code of the outer loop.

Previously i showed examples only with literals. Let’s see that this works also with variables:

SQL> create or replace procedure p_test_deterministic as 2 v varchar2(10); 3 p varchar2(10); 4 begin 5 for i in 1..3 loop 6 v:=xt_print_deterministic('before'); 7 p:=i; 8 for j in 1..10 loop 9 v:=i; 10 v:=xt_print_deterministic(p); 11 end loop; 12 v:=xt_print_deterministic('after'); 13 end loop; 14 end; 15 / Procedure created. SQL> exec p_test_deterministic(); before after 1 after 2 after 3 PL/SQL procedure successfully completed.

Example showing that optimization will be disabled if exists non-deterministic function in scope:

And now more funny example – Try to guess what will be displayed:

create or replace function xt_print_deterministic(p varchar2) return varchar2 deterministic as begin dbms_output.put_line(p); return p; end; / create or replace procedure p_test_deterministic(p in varchar2) as v varchar2(30); a varchar2(30); begin for i in 1..3 loop v:=xt_print_deterministic('before'); for j in 1..10 loop v:=xt_print_deterministic(p); if p='1' then a:=sys_context('userenv','sid'); else a:='1'; end if; end loop; v:=xt_print_deterministic('after'); end loop; end; / set serverout on; exec p_test_deterministic(1); exec p_test_deterministic(2);

**Instead of conclusion:**

I’m absolutly agree with what Steven Feuerstein said:

“So at this point, it looks that the applicability of this new optimization will be fairly narrow.”

Nice article! I have some questions though regarding your 1st test case

1) Could you explain why optimization of outer loop was stopped on inner loop?

2) If we replace two lines in the inner loop with “null;”, why we’d see “after” three times (if we comment out inner loop completely, then we’d see “after” only 1 time)?

Thanks!!

Grigoriy, thanks for positive feedback!

I think, both questions have the same answer:

Such optimization runs by every loop separately. I’m not sure, but it seems like when optimization finds nested loop, it stops and starts optimization of this next loop. So we cannot get unnesting of several multilevel nested loops.

Best regards,

Sayan

Sayan,

I was thinking about that, but then I’d expect to see “before” 3 times as well because the outer optimization stops in the inner loop. What is the difference then between BEFORE and AFTER in that case?

When i wrote “stops”, i mean that the optimization of this loop just ends on this step and goes to next loop, but not breaks optimization at all.