[20180531]函数调用与递归.txt

--//前几天我发现开发把一个计算公式建立为函数,我本想这样不好,因为执行select f(x)...,的每行都会调用自定义的函数.
--//我本以为这样会产生大量的递规调用.但是还是有点吃惊,如果函数里面没有一些select语句,这样的调用自定义函数,不会
--//产生递规,当然我个人这样还是不提倡这样的方式.

1.环境:
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

grant execute on sys.dbms_lock to scott;

CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
--//  sys.dbms_lock.sleep(seconds);
  RETURN seconds;
END;
/

2.测试1:
set autot traceonly
select rowid,dept.*,sleep(1) from dept ;
set autot off

SCOTT@test01p> select rowid,dept.*,sleep(1) from dept ;
Execution Plan
---------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1021  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
--//可以发现没有递归,如果第一次存在递归,多执行计划在查看.(以下测试采用相似的方式).

SCOTT@test01p> set autot off

3.测试2:
--//如果修改如下:
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER AS
BEGIN
  sys.dbms_lock.sleep(seconds);
  RETURN seconds;
END;
/

set autot traceonly
select rowid,dept.*,sleep(1) from dept ;
set autot off

SCOTT@test01p> select rowid,dept.*,sleep(1) from dept ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1021  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
--//可以发现调用sys.dbms_lock.sleep也没有出现递归.


4.测试3:
--//如果修改如下:
CREATE OR REPLACE FUNCTION sleep (seconds IN NUMBER)
RETURN NUMBER
is
d_date date;
BEGIN
  select sysdate into d_date from dual;
  RETURN seconds;
END;
/

set autot traceonly
select rowid,dept.*,sleep(1) from dept ;
set autot off

SCOTT@test01p> select rowid,dept.*,sleep(1) from dept ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    80 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1021  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

--//可以发现只有在函数里面执行sql语句,才存在递归调用.