CREATE TABLE DS1
(
X INTEGER NOT NULL PRIMARY KEY,
CNT INTEGER
) IN USERSPACE1;
CREATE TABLE DS2
(
X INTEGER NOT NULL,
Y INTEGER NOT NULL,
PRIMARY KEY (X,Y)
) IN USERSPACE1;
--新增測試資料
--DS1 X:1~100，共100筆資料
INSERT INTO DS1(X)
WITH N(X) AS (
SELECT 1 X
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.X + 1
FROM N
WHERE N.X + 1 <= 100
)
SELECT N.X FROM N;
--DS2 X:1~100 Y:1~100 CROSS JOIN，共10000筆資料
INSERT INTO DS2
WITH N(X) AS (
SELECT 1 X
FROM SYSIBM.SYSDUMMY1
UNION ALL
SELECT N.X + 1
FROM N
WHERE N.X + 1 <= 100
)
SELECT N.X , N1.X
FROM N,N N1;
--將以下SQL Statement存成.sql file ex:1.sql
--(不要斷行，也不要加結束符號ex:「;」)
MERGE INTO DS1
USING (SELECT X,COUNT(*) CNT
FROM DS2
GROUP BY X) DS2 ON DS2.X = DS1.X
WHEN MATCHED THEN
UPDATE SET DS1.CNT = DS2.CNT
--使用Explain SQL Tool
C:\>db2expln -database CDIDB2 -g -f c:\1.sql -o c:\1.log
--開啟1.log
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "ORION"
SQL Statement:
MERGE INTO DS1 USING (
SELECT X, COUNT(*)CNT
FROM DS2
GROUP BY X)DS2 ON DS2.X =DS1.X
WHEN MATCHED
THEN
UPDATE SET DS1.CNT =DS2.CNT
Section Code Page = 1208
Estimated Cost = 89.078125
Estimated Cardinality = 1.000000
Access Table Name = ORION.DS2 ID = 2,143
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 2
| | | #Sort Key Columns = 1
| | | | Key 1: X (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 25
| | | | Row Width = 12
| | | Piped
| | | Buffered Partial Aggregation
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 2
| Relation Scan
| | Prefetch: Eligible
Final Aggregation
| Group By
| Column Function(s)
Right Outer Hash Join
| Early Out: Single Match Per Inner Row
| Estimated Build Size: 4705
| Estimated Probe Size: 7843
| Access Table Name = ORION.DS1 ID = 2,142
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Isolation Level: Read Stability
| | Lock Intents
| | | Table: Intent Exclusive
| | | Row : Exclusive
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Residual Predicate(s)
| #Predicates = 2
Establish Row Position
| Access Table Name = ORION.DS1 ID = 2,142
Update: Table Name = ORION.DS1 ID = 2,142
| Update Predicate(s)
| | #Predicates = 1
End of section
Optimizer Plan:
UPDATE
( 2)
/ \
FETCH Table:
( 3) ORION
/ \ DS1
FILTER Table:
( 4) ORION
| DS1
HSJOIN
( 5)
/ \
TBSCAN GRPBY
( 6) ( 7)
| |
Table: TBSCAN
ORION ( 8)
DS1 |
SORT
( 9)
|
TBSCAN
( 10)
|
Table:
ORION
DS2
--將以下SQL Statement存成.sql file ex:2.sql
--(不要斷行，也不要加結束符號ex:「;」)
UPDATE DS1 SET CNT = (SELECT COUNT(*)
FROM DS2
WHERE DS2.X = DS1.X
GROUP BY DS2.X)
WHERE DS1.X IN (SELECT X FROM DS2 GROUP BY X)
--使用Explain SQL Tool
C:\>db2expln -database CDIDB2 -g -f c:\2.sql -o c:\2.log
--開啟2.log
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = No
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC", "ORION"
SQL Statement:
UPDATE DS1 SET CNT =(
SELECT COUNT(*)
FROM DS2
WHERE DS2.X =DS1.X
GROUP BY DS2.X)
WHERE DS1.X IN (
SELECT X
FROM DS2
GROUP BY X)
Section Code Page = 1208
Estimated Cost = 463.691986
Estimated Cardinality = 25.000000
Access Table Name = ORION.DS1 ID = 2,142
| Index Scan: Name = SYSIBM.SQL080703112552650 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: X (Ascending)
| #Columns = 1
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Data Prefetch: Eligible 0
| Index Prefetch: None
| Lock Intents
| | Table: Intent Exclusive
| | Row : Exclusive
Merge Join
| Early Out: Single Match Per Outer Row
| Access Table Name = ORION.DS2 ID = 2,143
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Predicate(s)
| | | Insert Into Sorted Temp Table ID = t1
| | | | #Columns = 1
| | | | #Sort Key Columns = 1
| | | | | Key 1: X (Ascending)
| | | | Sortheap Allocation Parameters:
| | | | | #Rows = 167
| | | | | Row Width = 8
| | | | Piped
| Sorted Temp Table Completion ID = t1
| Access Temp Table ID = t1
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
| | Sargable Predicate(s)
| | | Predicate Aggregation
| | | | Group By
| Aggregation Completion
| | Group By
Nested Loop Join
| Piped Inner
| Access Table Name = ORION.DS2 ID = 2,143
| | Index Scan: Name = SYSIBM.SQL080703112635470 ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: X (Ascending)
| | | | 2: Y (Ascending)
| | #Columns = 1
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
| | Sargable Index Predicate(s)
| | | Predicate Aggregation
| | | |Group By
| | | | Column Function(s)
| Aggregation Completion
| | Group By
| | Column Function(s)
Update: Table Name = ORION.DS1 ID = 2,142
End of section
Optimizer Plan:
UPDATE
( 2)
/---/ \--\
NLJOIN Table:
( 3) ORION
/-----/ \-----\ DS1
MSJOIN GRPBY
( 4) ( 12)
/ \---\ |
FETCH * IXSCAN
( 5) * ( 13)
/ \ | / \--\
IXSCAN Table: GRPBY Index: Table:
( 5) ORION ( 8) SYSIBM ORION
| DS1 | SQL080703112635470 DS2
Index: TBSCAN
SYSIBM ( 9)
SQL080703112552650 |
SORT
( 10)
|
TBSCAN
( 11)
|
Table:
ORION
DS2
※ so as Merge vs Delete
