FORALL执行UPDATE语句（二）

在ITPUB上看到一个FORALL执行UPDATE的帖子，觉得有点意思，简单记录一下。

这一篇分析UPDATE的FORALL与INSERT、DELETE语句实现上不同的根源。

原帖地址：http://www.itpub.net/thread-1443896-1-1.html

FOR ALL语法浅析：http://yangtingkun.itpub.net/post/468/198828

FORALL执行UPDATE语句：http://yangtingkun.itpub.net/post/468/518933

FORALL执行DELETE语句：http://yangtingkun.itpub.net/post/468/519691

 

 

在前面的测试中发现UPDATE语句很特别，INSERT和DELETE语句都使用的是数组绑定，一次执行，而UPDATE语句则执行了多次。

导致UPDATE语句特殊的原因是在没有指定WHERE语句中条件的情况下，设置了SET语句中的绑定，造成了对于SET语句中数组绑定的每个值，UPDATE都要运行一次。

而语句的处理上，实际上UPDATE与INSERT和DELETE没有什么区别，仍然采用的是数组绑定一次执行的方式。下面给出了几种不同情况的UPDATE语句，简单分析一下：

SQL> CREATE TABLE T AS
2 SELECT ROWNUM ID, TNAME NAME FROM TAB;

表已创建。

SQL> EXEC DBMS_SESSION.SESSION_TRACE_ENABLE

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 FORALL I IN V_ID.FIRST..V_ID.LAST
9 UPDATE T
10 SET NAME = LOWER(NAME)
11 WHERE ID = V_ID(I);
12 END;
13 /

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_ID T_ID;
4 TYPE T_NAME IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5 V_NAME T_NAME;
6 BEGIN
7 SELECT ID, NAME
8 BULK COLLECT INTO V_ID, V_NAME
9 FROM T;
10 FORALL I IN V_ID.FIRST..V_ID.LAST
11 UPDATE T
12 SET NAME = LOWER(V_NAME(I))
13 WHERE ID = V_ID(I);
14 END;
15 /

PL/SQL 过程已成功完成。

SQL> DECLARE
2 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3 V_ID T_ID;
4 TYPE T_NAME IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
5 V_NAME T_NAME;
6 BEGIN
7 SELECT ID, NAME
8 BULK COLLECT INTO V_ID, V_NAME
9 FROM T;
10 FORALL I IN V_NAME.FIRST..V_NAME.LAST
11 UPDATE T
12 SET NAME = LOWER(V_NAME(I));
13 END;
14 /

PL/SQL 过程已成功完成。

SQL> EXEC DBMS_SESSION.SESSION_TRACE_DISABLE

PL/SQL 过程已成功完成。

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

SPID
------------------------
4396

下面利用tkprof来分析trace文件：

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

E:\oracle\diag\rdbms\test112\test112\trace>tkprof test112_ora_4396.trc output.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on 星期六 6月 25 00:11:47 2011

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

检查格式化后的trace：

TKPROF: Release 11.2.0.1.0 - Development on 星期六 6月 25 00:11:47 2011

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

Trace file: test112_ora_4396.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
********************************************************************************

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

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;
 FORALL I IN V_ID.FIRST..V_ID.LAST
  UPDATE T
  SET NAME = LOWER(NAME)
  WHERE ID = V_ID(I);
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.07          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.01       0.07          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        8.20          8.20
********************************************************************************
.
.
.
********************************************************************************

SQL ID: 92td25bnxfh1y
Plan Hash: 931696821
UPDATE T SET NAME = LOWER(NAME)
WHERE
 ID = :B1

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

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  UPDATE  T (cr=21 pr=0 pw=0 time=0 us)
      7   TABLE ACCESS FULL T (cr=21 pr=0 pw=0 time=0 us cost=3 size=30 card=1)

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

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

DECLARE
 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 V_ID T_ID;
 TYPE T_NAME IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
 V_NAME T_NAME;
BEGIN
 SELECT ID, NAME
 BULK COLLECT INTO V_ID, V_NAME
 FROM T;
 FORALL I IN V_ID.FIRST..V_ID.LAST
  UPDATE T
  SET NAME = LOWER(V_NAME(I))
  WHERE ID = V_ID(I);
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          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.00          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        8.21          8.21
********************************************************************************

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

SQL ID: c74pvwv4an74r
Plan Hash: 931696821
UPDATE T SET NAME = LOWER(:B1 )
WHERE
 ID = :B2

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

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  UPDATE  T (cr=21 pr=0 pw=0 time=0 us)
      7   TABLE ACCESS FULL T (cr=21 pr=0 pw=0 time=0 us cost=3 size=30 card=1)

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

DECLARE
 TYPE T_ID IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
 V_ID T_ID;
 TYPE T_NAME IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
 V_NAME T_NAME;
BEGIN
 SELECT ID, NAME
 BULK COLLECT INTO V_ID, V_NAME
 FROM T;
 FORALL I IN V_NAME.FIRST..V_NAME.LAST
  UPDATE T
  SET NAME = LOWER(V_NAME(I));
END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          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.00          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                       2        0.00          0.00
  SQL*Net message from client                     2        9.42         16.16
  SQL*Net break/reset to client                   2        0.00          0.00
********************************************************************************

SQL ID: 0x6ddr8akzrjt
Plan Hash: 931696821
UPDATE T SET NAME = LOWER(:B1 )

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

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  UPDATE  T (cr=21 pr=0 pw=0 time=0 us)
     49   TABLE ACCESS FULL T (cr=21 pr=0 pw=0 time=96 us cost=3 size=119 card=7)

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

.
.
.

可以看到，本质上UPDATE和INSERT、DELETE没有区别，只是由于缺少WHERE语句的UPDATE语句在数组绑定的时候构成了类似笛卡尔积的更新模式，而事实上这个SQL本身就是错误的。

上一篇得到的结论是由于使用触发器的方式只能看到现象，而实际的运行次数，只能通过TRACE的方式来获取。

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-700713/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-700713/