/*
Update 的語法在 Performance tuning 上被我奉為萬靈丹的 Merge
原來也有不能勝出的時候。
My Scenario:
Table t: col1 以日期帶序號為單號。 Ex:20090903001
Table t1: col1 以單號帶項次。 Ex:2009090300101
2009090300102
2009090300103
2009090300104
2009090300105
join 條件: t.col1 = substr(t1.col1,1,11)，將 t 的其它欄位值更新至 t1
Merge 語法的效能就差到不行，反而是 Update 的效能超快
*/
/* 建立測試 table t 並新增資料 */
create table t
(
col1 char(11),
col2 char(8)
) in userspace1;
INSERT INTO T
WITH N (COL0,COL1,COL2) AS (
SELECT 1 COL0,SUBSTR(CHAR(INT(DATE(SYSDATE))),1,8) ||
SUBSTR(CHAR_OLD(DECIMAL(1,3,0)),1,3),
CHAR(INT(DATE(SYSDATE)))
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.COL0 + 1,SUBSTR(CHAR(INT(DATE(SYSDATE))),1,8) ||
SUBSTR(CHAR_OLD(DECIMAL(N.COL0 + 1,3,0)),1,3),
N.COL2
FROM N
WHERE N.COL0 + 1 <= 100)
SELECT COL1,COL2 FROM N;
create table t1
(
col1 char(13),
col2 char(8)
) in userspace1;
INSERT INTO T1(COL1)
WITH N (COL0,COL1,OCOL1) AS (
SELECT 1 COL0,COL1 || SUBSTR(CHAR_OLD(DECIMAL(1,2,0)),1,2),
COL1 OCOL1
FROM T
WHERE MOD(INT(RIGHT(COL1,2)),2) = 0
UNION ALL
SELECT N.COL0 + 1,RTRIM(N.OCOL1) ||
SUBSTR(CHAR_OLD(DECIMAL(N.COL0 + 1,2,0)),1,2),
N.OCOL1
FROM N
WHERE N.COL0 + 1 <= 5)
SELECT COL1 FROM N ORDER BY COL1;
merge into t1 using t on t.col1 = substr(t1.col1,1,11)
when matched then
update set t1.col2 = t.col2
update t1 set col2 = (select t.col2
from t where t.col1 = substr(t1.col1,1,11))
C:\>db2expln -database ORION -g -stmtfile merge.sql -o merge.log
/* 開啟 merge.log */C:\>notepad merge.log
Statement:
merge into t1 using t on t.col1 =substr(t1.col1, 1, 11)
when matched
then
update set t1.col2 =t.col2
Section Code Page = 1208
Estimated Cost = 3692.811523
Estimated Cardinality = 240.000000
Access Table Name = ADMINISTRATOR.T ID = 2,9479
| #Columns = 2
| Skip Inserted Rows
| Avoid Locking Committed Data
| Currently Committed for Cursor Stability
| May participate in Scan Sharing structures
| Scan may start anywhere and wrap, for completion
| Scan can be throttled in scan sharing management
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Left Outer Nested Loop Join
| Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
| | #Columns = 0
| | Skip Inserted Rows
| | Evaluate Block/Data Predicates Before Locking Committed Row
| | May participate in Scan Sharing structures
| | Fast scan, for purposes of scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Isolation Level: Read Stability
| | Lock Intents
| | | Table: Intent Exclusive
| | | Row : Update
| | Sargable Predicate(s)
| | | #Predicates = 1
Insert Into Sorted Temp Table ID = t1
| #Columns = 3
| #Sort Key Columns = 1
| | Key 1: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 250.000000
| | Row Width = 28
| Piped
Access Temp Table ID = t1
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
Residual Predicate(s)
| #Predicates = 4
Residual Predicate(s)
| #Predicates = 1
Establish Row Position
| Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
Update: Table Name = ADMINISTRATOR.T1 ID = 2,9480
| Update Predicate(s)
| | #Predicates = 1
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
240
UPDATE
( 2)
3692.81
/--/ \
240 250
FETCH Table:
( 3) ADMINISTRATOR
1877.31 T1
/ \
240 250
FILTER Table:
( 4) ADMINISTRATOR
61.6852 T1
|
250
FILTER
( 5)
61.6458
|
250
TBSCAN
( 6)
61.2642
|
250
SORT
( 7)
61.2416
|
250
NLJOIN
( 8)
61.1053
/ \---\
100 *
TBSCAN |
( 9) 250
15.2041 Table:
| ADMINISTRATOR
100 T1
Table:
ADMINISTRATOR
T
C:\>db2expln -database ORION -g -stmtfile update.sql -o update.log
/* 開啟 update.log */C:\>notepad update.log
Statement:
update t1 set col2 =
(select t.col2
from t
where t.col1 =substr(t1.col1, 1, 11))
Section Code Page = 1208
Estimated Cost = 1952.518555
Estimated Cardinality = 250.000000
Access Table Name = ADMINISTRATOR.T1 ID = 2,9480
| #Columns = 1
| Skip Inserted Rows
| May participate in Scan Sharing structures
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Nested Loop Join
| Piped Inner
| Access Table Name = ADMINISTRATOR.T ID = 2,9479
| | #Columns = 1
| | Skip Inserted Rows
| | Avoid Locking Committed Data
| | Currently Committed for Cursor Stability
| | May participate in Scan Sharing structures
| | Scan may start anywhere and wrap, for completion
| | Fast scan, for purposes of scan sharing management
| | Scan can be throttled in scan sharing management
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | #Predicates = 1
Update: Table Name = ADMINISTRATOR.T1 ID = 2,9480
End of section
Optimizer Plan:
Rows
Operator
(ID)
Cost
250
UPDATE
( 2)
1952.52
/--/ \
250 250
NLJOIN Table:
( 3) ADMINISTRATOR
61.3672 T1
/ \
250 1
TBSCAN TBSCAN
( 4) ( 5)
22.8603 15.2357
| |
250 100
Table: Table:
ADMINISTRATOR ADMINISTRATOR
T1 T
Merge 的 Estimated Cost = 3692.811523
Update 的 Estimated Cost = 1952.518555
