Re: ouch

SQL> select substr('BonzoDogDooDah', 4, 7) from dual;
SUBSTR(
-------
zoDogDo
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        436  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
SQL> select substring('BonzoDogDooDah', 4, 7) from dual;
SUBSTRING('BONZODOGDOODAH',4,7)
--------------------------------------------------------------------------------
zoDogDo

Statistics
----------------------------------------------------------
         22  recursive calls                                            <===== 22 times more work for the function
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        439  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL>
 
It's wasteful and  shortsighted, in my opinion.  Why trade 1 recursive call for 22 by writing this function?

David Fitzjarrell


From: "Uzzell, Stephan" <SUzzell_at_MICROS.COM>
To: Oracle L <oracle-l_at_freelists.org>
Sent: Wednesday, August 31, 2011 1:31 PM
Subject: ouch


Found a gem in one of my databases today:
 
FUNCTION                        "SUBSTRING"
(INSTRING IN varchar2,
STARTPOS IN number,
LENGTH   IN number)
RETURN varchar2
IS
  RESULT varchar2(8000);
BEGIN
select substr(INSTRING, STARTPOS, LENGTH)
into RESULT
from DUAL;
RETURN(RESULT);
END;
 
I’m offended by the sheer aesthetics of this. And I want to go yell at the developers that this is wrong. But I’d need a better argument than that it offends me. Is there some way to quantify the impact (if any) of wrapping a built-in function like this?
 
Stephan Uzzell

--
http://www.freelists.org/webpage/oracle-l