[20180531]函式呼叫與遞迴.txt

[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語句,才存在遞迴呼叫.