Insert slow in DPF environment.

Prev: Table Partitioning the way to go?
Next: cheap chanel bags

From: Anwei Shen on 6 Jul 2010 14:43

---

DB2 DPF V9.1.7 on Linux

Have partition table based on PK like this.

CREATE TABLE DOC (
"SOURCE_ID" INTEGER NOT NULL ,
"DRN" BIGINT NOT NULL ,
"DOCTYPE" CHAR(10) NOT NULL ,
"DW_INSERT_TS" TIMESTAMP NOT NULL ,
"DW_UPDATE_TS" TIMESTAMP NOT NULL ,

....
)
DISTRIBUTE BY HASH("DRN", "SOURCE_ID") ;

Create table LOAD_TAB(
DW_LOAD_TS timestamp );

create table test
(
DRN BIGINT NOT NULL,
SOURCE_ID INTEGER NOT NULL,
CHANGE_IND CHAR(1),
....
PRIMARY KEY (DRN, SOURCE_ID)
)
DISTRIBUTE BY HASH (DRN, SOURCE_ID) ;

The following query is to find only the changed records since last
run, it took seconds to 86K records, which is OK

WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION ALL
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT * FROM ALL_CHANGED_DOC

;

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER
(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC

;

But when insert into a partitioned table, it took 5 minutes.

insert into dmtrstg.test (DRN, SOURCE_ID, CHANGE_IND, ANCHORID,
RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION ALL
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT * FROM ALL_CHANGED_DOC

;

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER
(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >= L.DW_LOAD_TS THEN 'I'
END CHANGE_IND
FROM DOC D,
LOAD_TAB L
),
UPDATED_DOC_ANCHORID (ANCHORID) AS (
SELECT
D.ANCHORID
FROM DOC D JOIN
LOAD_TAB L
ON D.CURR_IND=1 AND D.DW_UPDATE_TS >= L.DW_LOAD_TS
AND D.DW_INSERT_TS < L.DW_LOAD_TS
),
UPDATED_DOC_BY_ANCHORID (DRN, SOURCE_ID) AS (
SELECT
D.DRN, D.SOURCE_ID
FROM DOC D
JOIN UPDATED_DOC_ANCHORID U
ON D.ANCHORID = U.ANCHORID
),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID, CHANGE_IND) AS (
SELECT
D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID) NOT IN (SELECT DRN, SOURCE_ID FROM
New_Obsolete_Doc)
),
ALL_CHANGED_DOC(DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN,
DOCTYPE) AS (
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
UNION
SELECT
U.DRN, U.SOURCE_ID, U.CHANGE_IND,
D.ANCHORID, D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOC D
ON U.DRN = D.DRN AND U.SOURCE_ID=D.SOURCE_ID
)
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC

;

From: Anwei Shen on 6 Jul 2010 14:49

---

DB Snapshot show Billions of Reads from temp table on all partitions.

Explain from both statement are similar

For the query
==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM",


Statement:

WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS
THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >=L.DW_LOAD_TS
THEN 'I' END CHANGE_IND
FROM DOCHEADER D, LOAD_TAB L ),
UPDATED_DOC_ANCHORID (ANCHORID)AS (
SELECT D.ANCHORID
FROM DOCHEADER D JOIN LOAD_TAB L ON
D.CURR_IND=1 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS AND
D.DW_INSERT_TS < L.DW_LOAD_TS ), UPDATED_DOC_BY_ANCHORID
(DRN, SOURCE_ID)AS (
SELECT D.DRN, D.SOURCE_ID
FROM DOCHEADER D JOIN UPDATED_DOC_ANCHORID U ON
D.ANCHORID =U.ANCHORID ),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID,
CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID)NOT IN
(SELECT DRN, SOURCE_ID
FROM New_Obsolete_Doc)), ALL_CHANGED_DOC(DRN,
SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)AS
(
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN DOCHEADER D ON
U.DRN =D.DRN AND U.SOURCE_ID=
D.SOURCE_ID
UNION ALL
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOCHEADER D ON U.DRN =
D.DRN AND U.SOURCE_ID=D.SOURCE_ID )
SELECT *
FROM ALL_CHANGED_DOC


Section Code Page = 1208

Estimated Cost = 628178.625000
Estimated Cardinality = 196823328.000000

Coordinator Subsection - Main Processing:
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #6
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #5
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #4
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #7
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Access Table Queue ID = q1 #Columns = 6
Return Data to Application
| #Columns = 6

Subsection #1:
(
| Access Table Queue ID = q2 #Columns = 8
| Nested Loop Join
| | Access Table Name = LOAD_TAB ID = 6,149
| | | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: DW_LOAD_TS (Ascending)
| | | #Columns = 1
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | | Index-Only Access
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
UNION
| Access Table Queue ID = q3 #Columns = 7
| Nested Loop Join
| | Access Table Name = LOAD_TAB ID = 6,149
| | | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: DW_LOAD_TS (Ascending)
| | | #Columns = 1
| | | #Key Columns = 1
| | | | Start Key: Exclusive Value
| | | | | | 1: ?
| | | | Stop Key: Inclusive Value
| | | | | | 1: ?
| | | Index-Only Access
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent Share
| | | | Row : Next Key Share
)
Insert Into Asynchronous Table Queue ID = q1
| Broadcast to Coordinator Node
| Rows Can Overflow to Temporary Table

Subsection #2:
Access Table Name = DOCHEADER ID = 6,5
| #Columns = 8
| Compressed Table
| Volatile Cardinality
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q2
| | | Broadcast to All Nodes of Subsection 1
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q2

Subsection #3:
Access Table Queue ID = q4 #Columns = 5
Hash Join
| Estimated Build Size: 400000
| Estimated Probe Size: 480000
| Access Table Queue ID = q7 #Columns = 3
Insert Into Asynchronous Table Queue ID = q3
| Broadcast to All Nodes of Subsection 1
| Rows Can Overflow to Temporary Table

Subsection #4:
Data Stream 1:
| Not Piped
| Access Table Queue ID = q5 #Columns = 2
| Insert Into Temp Table ID = t1
| | #Columns = 2
End of Data Stream 1
Access Table Name = DOCHEADER ID = 6,5
| #Columns = 5
| Compressed Table
| Volatile Cardinality
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Residual Predicate(s)
| | #Predicates = 3
| | Access Data Stream 1
| | Access Temp Table ID = t1
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: Eligible
| | ALL Subquery
| | | Access Data Stream 1
| | Insert Into Asynchronous Table Queue ID = q4
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q4

Subsection #5:
Access Table Name = DOCHEADER ID = 6,5
| Index Scan: Name = DOCHEADER_PK ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DRN (Ascending)
| | | 2: SOURCE_ID (Ascending)
| #Columns = 2
| Compressed Table
| Volatile Cardinality
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: Eligible 23
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Nested Loop Join
| Data Stream 2:
| | Not Piped
| | Access Table Queue ID = q6 #Columns = 0
| | Insert Into Temp Table ID = t2
| | | #Columns = 0
| End of Data Stream 2
| Access Temp Table ID = t2
| | #Columns = 0
| | Single Record
| | Relation Scan
| | | Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q5
| Broadcast to All Nodes of Subsection 4
| Rows Can Overflow to Temporary Table

Subsection #6:
Access Table Name = LOAD_TAB ID = 6,149
| Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DW_LOAD_TS (Ascending)
| #Columns = 0
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q6
| | | Broadcast to All Nodes of Subsection 5
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q6

Subsection #7:
Access Table Name = DOCHEADER ID = 6,5
| Index Scan: Name = DB2P0003.IDX_DOCHEADER2 ID = 2
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: CURR_IND (Ascending)
| #Columns = 4
| Compressed Table
| Volatile Cardinality
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 1
| | Stop Key: Inclusive Value
| | | | 1: 1
| Data Prefetch: Eligible 891
| Index Prefetch: Eligible 891
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q7
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q7

End of section

From: Anwei Shen on 6 Jul 2010 14:50

---

From the insert

==================== STATEMENT
==========================================

Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"SYSIBMADM",


Statement:

INSERT INTO DMTRSTG.DW_EXTRACT_DRIVER (DRN, SOURCE_ID,
CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)
WITH New_Obsolete_Doc (DRN, SOURCE_ID, CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID,
CASE
WHEN D.CURR_IND=0 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS
THEN 'D'
WHEN D.CURR_IND=1 AND D.DW_INSERT_TS >=L.DW_LOAD_TS
THEN 'I' END CHANGE_IND
FROM DOCHEADER D, LOAD_TAB L ),
UPDATED_DOC_ANCHORID (ANCHORID)AS (
SELECT D.ANCHORID
FROM DOCHEADER D JOIN LOAD_TAB L ON
D.CURR_IND=1 AND D.DW_UPDATE_TS >=L.DW_LOAD_TS AND
D.DW_INSERT_TS < L.DW_LOAD_TS ), UPDATED_DOC_BY_ANCHORID
(DRN, SOURCE_ID)AS (
SELECT D.DRN, D.SOURCE_ID
FROM DOCHEADER D JOIN UPDATED_DOC_ANCHORID U ON
D.ANCHORID =U.ANCHORID ),
UPDATED_DOC_EXCLUDE_New_Obsolete(DRN, SOURCE_ID,
CHANGE_IND)AS (
SELECT D.DRN, D.SOURCE_ID, 'U'
FROM UPDATED_DOC_BY_ANCHORID D
WHERE (DRN, SOURCE_ID)NOT IN
(SELECT DRN, SOURCE_ID
FROM New_Obsolete_Doc)), ALL_CHANGED_DOC(DRN,
SOURCE_ID, CHANGE_IND, ANCHORID, RELATEDDOCDRN, DOCTYPE)AS
(
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM New_Obsolete_Doc U JOIN DOCHEADER D ON
U.DRN =D.DRN AND U.SOURCE_ID=
D.SOURCE_ID
UNION
SELECT U.DRN, U.SOURCE_ID, U.CHANGE_IND, D.ANCHORID,
D.RELATEDDOCDRN, D.DOCTYPE
FROM UPDATED_DOC_EXCLUDE_New_Obsolete U JOIN
DOCHEADER D ON U.DRN =
D.DRN AND U.SOURCE_ID=D.SOURCE_ID )
SELECT DRN, SOURCE_ID, CHANGE_IND, ANCHORID,
RELATEDDOCDRN, DOCTYPE
FROM ALL_CHANGED_DOC


Section Code Page = 1208

Estimated Cost = 626511.687500
Estimated Cardinality = 14257.333008

Coordinator Subsection - Main Processing:
Distribute Subsection #4
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #6
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #5
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #9
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #8
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #7
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 0, 1, 2, 3, 4, 5, 6, 7, 8

Subsection #1:
Access Table Queue ID = q1 #Columns = 6
Insert Into Sorted Temp Table ID = t1
| #Columns = 6
| #Sort Key Columns = 6
| | Key 1: (Ascending)
| | Key 2: (Ascending)
| | Key 3: (Ascending)
| | Key 4: (Ascending)
| | Key 5: (Ascending)
| | Key 6: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 14258.000000
| | Row Width = 36
| Piped
| Duplicate Elimination
Access Temp Table ID = t1
| #Columns = 6
| Relation Scan
| | Prefetch: Eligible
Insert: Table Name = DMTRSTG.DW_EXTRACT_DRIVER ID = 2,59

Subsection #2:
(
| Access Table Queue ID = q2 #Columns = 6
| | Output Sorted and Unique
| | | #Key Columns = 3
| | | | Key 1: (Ascending)
| | | | Key 2: (Ascending)
| | | | Key 3: (Ascending)
UNION
| Access Table Queue ID = q4 #Columns = 1
| Hash Join
| | Early Out: Single Match Per Outer Row
| | Estimated Build Size: 352000
| | Estimated Probe Size: 304000
| | Access Table Queue ID = q6 #Columns = 5
)
Insert Into Asynchronous Table Queue ID = q1
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #3:
Access Table Queue ID = q3 #Columns = 8
Nested Loop Join
| Access Table Name = LOAD_TAB ID = 6,149
| | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: DW_LOAD_TS (Ascending)
| | #Columns = 1
| | #Key Columns = 0
| | | Start Key: Beginning of Index
| | | Stop Key: End of Index
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Insert Into Sorted Temp Table ID = t2
| #Columns = 6
| #Sort Key Columns = 3
| | Key 1: (Ascending)
| | Key 2: (Ascending)
| | Key 3: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 20273928.000000
| | Row Width = 48
| Piped
| Duplicate Elimination
Access Temp Table ID = t2
| #Columns = 6
| Relation Scan
| | Prefetch: Eligible
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q2
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q2

Subsection #4:
Access Table Name = DOCHEADER ID = 6,5
| #Columns = 8
| Compressed Table
| Volatile Cardinality
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q3
| | | Broadcast to All Nodes of Subsection 3
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q3

Subsection #5:
Access Table Queue ID = q5 #Columns = 3
Nested Loop Join
| Access Table Name = LOAD_TAB ID = 6,149
| | Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: DW_LOAD_TS (Ascending)
| | #Columns = 1
| | Single Record
| | #Key Columns = 1
| | | Start Key: Exclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Insert Into Asynchronous Table Queue ID = q4
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #6:
Access Table Name = DOCHEADER ID = 6,5
| Index Scan: Name = DB2P0003.IDX_DOCHEADER2 ID = 2
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: CURR_IND (Ascending)
| #Columns = 4
| Compressed Table
| Volatile Cardinality
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: 1
| | Stop Key: Inclusive Value
| | | | 1: 1
| Data Prefetch: Eligible 891
| Index Prefetch: Eligible 891
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q5
| | | Broadcast to All Nodes of Subsection 5
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q5

Subsection #7:
Data Stream 1:
| Not Piped
| Access Table Queue ID = q7 #Columns = 2
| Insert Into Temp Table ID = t3
| | #Columns = 2
End of Data Stream 1
Access Table Name = DOCHEADER ID = 6,5
| #Columns = 5
| Compressed Table
| Volatile Cardinality
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Residual Predicate(s)
| | #Predicates = 3
| | Access Data Stream 1
| | Access Temp Table ID = t3
| | | #Columns = 2
| | | Relation Scan
| | | | Prefetch: Eligible
| | ALL Subquery
| | | Access Data Stream 1
| | Insert Into Asynchronous Table Queue ID = q6
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q6

Subsection #8:
Access Table Name = DOCHEADER ID = 6,5
| Index Scan: Name = DOCHEADER_PK ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DRN (Ascending)
| | | 2: SOURCE_ID (Ascending)
| #Columns = 2
| Compressed Table
| Volatile Cardinality
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: Eligible 23
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
Nested Loop Join
| Data Stream 2:
| | Not Piped
| | Access Table Queue ID = q8 #Columns = 0
| | Insert Into Temp Table ID = t4
| | | #Columns = 0
| End of Data Stream 2
| Access Temp Table ID = t4
| | #Columns = 0
| | Single Record
| | Relation Scan
| | | Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q7
| Broadcast to All Nodes of Subsection 7
| Rows Can Overflow to Temporary Table

Subsection #9:
Access Table Name = LOAD_TAB ID = 6,149
| Index Scan: Name = DWDC_HST.PK_DM_LOAD_TAB ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: DW_LOAD_TS (Ascending)
| #Columns = 0
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: None
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q8
| | | Broadcast to All Nodes of Subsection 8
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q8

End of section

From: Anwei Shen on 7 Jul 2010 14:55

---

application status shows

Pending remote request

 | 
Pages: 1
Prev: Table Partitioning the way to go?
Next: cheap chanel bags