SUM is slower than loop

Question

I am currently trying to optimize a view, that wasn't written by me. It is really complex, with a lot views using function that use views and so on. So, playing around with what I can optimize I have something I can't really 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: loops are bad, you can do the same with 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 good actually slower, by the factor of ~2. Does sum just don't like functions? Can someone explain this?

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

Answer 1

The difference in run time is down to PL/SQL / SQL context switching. When embedding a PL/SQL function into a SQL statement Oracle needs to switch back and forth between the two engines. Consider this very simplistic test which nevertheless 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.

Simple Query performing 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

Simple Query Performing Aggregation with PL/SQL function call

  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

So the pure SQL example takes 0.36 of a second whilst the one with the SQL / PLSQL context switch takes slightly more than twice the time at 0.87 seconds.