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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 一條update SQL語句是如何執行的SQL
- MySQL語句執行分析（二）MySql
- SQLite語句（二）：INSERT DELETE UPDATE SELECTSQLitedelete
- 執行update語句，用沒用到索引，區別大嗎？索引
- 深入解讀MySQL InnoDB儲存引擎Update語句執行過程MySql儲存引擎
- 【SQL】10 SQL UPDATE 語句SQL
- mySQL 執行語句執行順序MySql
- MySQL中UPDATE語句裡SET後使用AND的執行過程和結果分析MySql
- MySQL -update語句流程總結MySql
- ORACLE多表關聯UPDATE語句Oracle
- MySQL的update語句避坑MySql
- MySQL探祕(二)：SQL語句執行過程詳解MySql
- SQL語句執行順序SQL
- MySQL語句執行分析（一）MySql
- sql語句如何執行的SQL
- Select語句執行順序
- 查詢Oracle正在執行的sql語句及執行該語句的使用者OracleSQL
- sql語句執行緩慢分析SQL
- mysql的sql語句執行流程MySql
- SQL 語句的執行順序SQL
- mysql 語句的執行順序MySql
- 後臺執行SQL語句(oracle)SQLOracle
- takes_ownership(s)語句執行
- mysql執行sql語句過程MySql
- python怎樣執行js語句PythonJS
- Mybatis 動態執行SQL語句MyBatisSQL
- 一個UPDATE語句引發的血案
- DBeaver如何生成select，update，delete，insert語句delete
- Laravel 獲取執行的sql語句LaravelSQL
- 一條更新語句的執行流程
- MySQL cron定時執行SQL語句MySql
- java連線oracle執行sql語句JavaOracleSQL
- 【Java】面試官靈魂拷問：if語句執行完else語句真的不會再執行嗎？Java面試
- Oracle執行語句跟蹤 使用sql trace實現語句追蹤OracleSQL
- finally語句在return執行之後，return返回之前執行
- KunlunDB功能之insert/update/delete...returning語句delete
- [20181119]sql語句執行緩慢分析.txtSQL
- SQL語句各子句的執行順序SQL
- mysql sql語句執行超時設定MySql