SUM is slower than a cycle

Question

I'm currently trying to optimize a view that was not written by me. It is really complicated, with a lot of views, using a function that uses views and so on. So, playing with what I can optimize, I have something that I cannot understand: I have this function:

create or replace FUNCTION at_get_tourenrechnungssumme_br (in_rechnr IN rechnungen.rechnr%TYPE) RETURN NUMBER IS CURSOR c1 ( int_rechnr IN rechnungen.rechnr%TYPE) IS SELECT (ROUND ( verrechnung.get_betrag (bt.buchid, bt.betrag_euro)*(1+b.mwst/100), 2)) betrag FROM buchungen_touren bt, v_buchkz b WHERE bt.rechnr = int_rechnr AND bt.storniert_jn = 0 AND bt.buchid = b.ID; int_return NUMBER (11, 2) := 0; BEGIN FOR c1_rec IN c1 (in_rechnr) LOOP int_return := (int_return + c1_rec.betrag); END LOOP; RETURN NVL (int_return, 0); END at_get_tourenrechnungssumme_br; 

I just thought: the loops are bad, you can do the same with the sum:

 create or replace FUNCTION at_get_tourenrechnungssumme_br (in_rechnr IN rechnungen.rechnr%TYPE) RETURN NUMBER IS int_return NUMBER (11, 2) := 0; BEGIN SELECT sum(ROUND ( verrechnung.get_betrag (bt.buchid, bt.betrag_euro)*(1+b.mwst/100), 2)) betrag into int_return FROM buchungen_touren bt, v_buchkz b WHERE bt.rechnr = in_rechnr AND bt.storniert_jn = 0 AND bt.buchid = b.ID; RETURN NVL (int_return, 0); END at_get_tourenrechnungssumme_br; 

The strange thing is that it is actually actually slower, twice as good. sum just don't like functions? Can someone explain this?

Edit: This is rather a theoretical question. The obvious solution is to avoid using functions (which I mostly do when I optimize the views, someone wrote), which I did, but I think the question is still interesting.

Answer 1

The difference in runtime comes down to switching the context of PL / SQL / SQL. When embedding a PL / SQL function in an Oracle SQL query, you must switch between the two engines. Consider this very simplified test, which nonetheless illustrates the difference.

Create a simple function

 SQL> CREATE OR REPLACE FUNCTION test (a IN number, b in NUMBER) RETURN NUMBER 2 AS 3 4 BEGIN 5 return a+b; 6 END; 7 / Function created. 

A simple query that performs addition and aggregation in pure SQL

  1 WITH 2 row_gen 3 AS ( 4 SELECT LEVEL as a, level as b 5 FROM DUAL 6 CONNECT BY LEVEL < 1000000 7 ) 8 SELECT SUM(a+b) 9* FROM row_gen SQL> / SUM(A+B) ---------- 1.0000E+12 Elapsed: 00:00:00.36 

A simple query that performs aggregation with a call to the PL / SQL function

  1 WITH 2 row_gen 3 AS ( 4 SELECT LEVEL as a, level as b 5 FROM DUAL 6 CONNECT BY LEVEL < 1000000 7 ) 8 SELECT SUM(test(b,b)) 9* FROM row_gen SQL> / SUM(TEST(B,B)) -------------- 1.0000E+12 Elapsed: 00:00:00.87 

Thus, a pure SQL example takes 0.36 seconds, while one with the SQL / PLSQL context switch takes a little more than double the time by 0.87 seconds.

Answer 2

In case of SUM, you can wait for a memory or disk. The optimizer can materialize the results of your function calls so that they can sum them after they have been previously calculated. In the PL / SQL loop, you explicitly discard previously read lines, reducing placement costs, and optimizing FIRST_ROWS may also mean that you can start working with math before the disk finishes reading all the data.

This is an assumption, and profiling is the only way to know for sure.