FORALL執行DELETE語句

首先建立一個測試表：

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE);

PL/SQL 過程已成功完成。

SQL> DECLARE
2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_ID T_ID;
4 BEGIN
5 SELECT ID
6 BULK COLLECT INTO V_ID
7 FROM T
8 WHERE ROWNUM < 100;
9 FORALL I IN V_ID.FIRST..V_ID.LAST
10 DELETE T WHERE ID = V_ID(I);
11 END;
12 /

PL/SQL 過程已成功完成。

SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE;

PL/SQL 過程已成功完成。

執行FORALL刪除後，找到當前會話的trace檔案：

SQL> SELECT SPID
2 FROM V$SESSION S, V$PROCESS P
3 WHERE S.PADDR = P.ADDR
4 AND SID = USERENV('SID');

SPID
------------------------
2956

由於詳細TRACE格式太長，不易閱讀，這裡利用TKPROF來分析trace是最佳選擇：

E:\>cd E:\oracle\diag\rdbms\test112\test112\trace

E:\oracle\diag\rdbms\test112\test112\trace>tkprof test112_ora_2956 output.txt

TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 24 08:50:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

對於格式化的trace，我們只取有關的內容：

TKPROF: Release 11.2.0.1.0 - Development on 星期五 6月 24 08:50:59 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: test112_ora_2956.trc
Sort options: default

*******************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
*******************************************************************************

SQL ID: 2xyvdw1vnhac8
Plan Hash: 0
BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(TRUE, TRUE); END;

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       0      0.00       0.00          0          0          0          0
Execute     1      0.00       0.01          0          0          0          1
Fetch       0      0.00       0.00          0          0          0          0
------- ------  -------- ---------- ---------- ---------- ----------  ---------
total       1      0.00       0.01          0          0          0          1

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       21.45         21.45
*******************************************************************************

DECLARE
 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 V_ID T_ID;
BEGIN
 SELECT ID
 BULK COLLECT INTO V_ID
 FROM T
 WHERE ROWNUM < 100;
 FORALL I IN V_ID.FIRST..V_ID.LAST
  DELETE T WHERE ID = V_ID(I);
END;

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.01          0          0          0          0
Execute     1      0.00       0.00          0          0          0          1
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       2      0.00       0.01          0          0          0          1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61 

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       13.51         13.51
*******************************************************************************

.
.
.
*******************************************************************************

SQL ID: avyzsyqmd74r4
Plan Hash: 508354683
SELECT ID
FROM
 T WHERE ROWNUM < 100

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.00          0          2          0          0
Execute     1      0.00       0.00          0          0          0          0
Fetch       1      0.00       0.00          0          4          0         99
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       3      0.00       0.00          0          6          0         99

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
     99  COUNT STOPKEY (cr=4 pr=0 pw=0 time=294 us)
     99   TABLE ACCESS FULL T (cr=4 pr=0 pw=0 time=98 us cost=2 size=757874 card=58298)

*******************************************************************************

SQL ID: 998sf5pg15v3q
Plan Hash: 3335594643
DELETE T
WHERE
 ID = :B1

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       1      0.00       0.00          0          0          0          0
Execute     1      0.21       0.47         19      13465        101         99
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       2      0.21       0.47         19      13465        101         99

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  DELETE  T (cr=13464 pr=19 pw=0 time=0 us)
     99   TABLE ACCESS FULL T (cr=13464 pr=19 pw=0 time=0 us cost=54 size=26 card=2)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                         6        0.00          0.00
  db file scattered read                          5        0.00          0.00
*******************************************************************************

.
.
.
*******************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call    count       cpu    elapsed       disk      query    current       rows
------- -----  -------- ---------- ---------- ---------- ----------  ---------
Parse       2      0.00       0.01          0          0          0          0
Execute     3      0.00       0.02          0          0          0          3
Fetch       0      0.00       0.00          0          0          0          0
------- -----  -------- ---------- ---------- ---------- ----------  ---------
total       5      0.00       0.04          0          0          0          3

Misses in library cache during parse: 2

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       21.45         34.97
.
.
.
*******************************************************************************
Trace file: test112_ora_2956.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       8  user  SQL statements in trace file.
       7  internal SQL statements in trace file.
      15  SQL statements in trace file.
      13  unique SQL statements in trace file.
     982  lines in trace file.
      35  elapsed seconds in trace file.

可以明顯的看到，對於DELETE語句，僅僅執行了一次，而處理的行數是99行，同樣，前面的SELECT BULK COLLECT INTO語句也僅FETCH了一次，而處理了99行。

對於FORALL語句，批次繫結陣列，而是的執行次數大大減少，減小了系統維護的開銷，也是的操作的效率更高。