dbTalk Databases Forums  

DB2 MERGE STATEMENT ARE using table scan

Go Back dbTalk Databases Forums Databases comp.databases.ibm-db2 DB2 MERGE STATEMENT ARE using table scan

comp.databases.ibm-db2 comp.databases.ibm-db2


Discuss DB2 MERGE STATEMENT ARE using table scan in the comp.databases.ibm-db2 forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 12:52 PM





Section Code Page = 1208

Estimated Cost = 9530.152344
Estimated Cardinality = 638.346680

Coordinator Subsection - Main Processing:
Distribute Subsection #6
Quote:
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 #3
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 = 1, 2, 3, 4, 5, 6, 7, 8
Subsection #1:
Data Stream 1:
Quote:
Not Piped
Access Table Queue ID = q1 #Columns = 331
Insert Into Temp Table ID = t1
| #Columns = 331
End of Data Stream 1
Access Temp Table ID = t1
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Insert: Table Name = TR_3.FACT ID = 14,5
Insert Predicate(s)
| #Predicates = 2
Subsection #2:
Data Stream 2:
Quote:
Not Piped
Data Stream 3:
| Not Piped
| Data Stream 4:
| | Not Piped
| | Access Table Queue ID = q2 #Columns = 333
| | Insert Into Temp Table ID = t2
| | | #Columns = 333
| End of Data Stream 4
| Access Temp Table ID = t2
| | #Columns = 333
| | Relation Scan
| | | Prefetch: Eligible
| Establish Row Position
| | Access Table Name = TR_3.FACT ID = 14,5
| Update: Table Name = TR_3.FACT ID = 14,5
| | Update Predicate(s)
| | | #Predicates = 2
| Insert Into Temp Table ID = t3
| | #Columns = 333
End of Data Stream 3
Access Temp Table ID = t3
| #Columns = 333
| Relation Scan
| | Prefetch: Eligible
Establish Row Position
| Access Table Name = TR_3.FACT ID = 14,5
Delete: Table Name = TR_3.FACT ID = 14,5
| Delete Predicate(s)
| | #Predicates = 2
Insert Into Temp Table ID = t4
| #Columns = 331
End of Data Stream 2
Access Temp Table ID = t4
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q1
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q1

Subsection #3:
Data Stream 5:
Quote:
Not Piped
Data Stream 6:
| Not Piped
| Access Table Queue ID = q3 #Columns = 337
| Insert Into Temp Table ID = t5
| | #Columns = 337
End of Data Stream 6
Access Temp Table ID = t5
| #Columns = 337
| Relation Scan
| | Prefetch: Eligible
Insert: Table Name = TR_3.FACT ID = 14,5
| Insert Predicate(s)
| | #Predicates = 1
Insert Into Temp Table ID = t6
| #Columns = 333
End of Data Stream 5
Access Temp Table ID = t6
Quote:
#Columns = 333
Relation Scan
| Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q2
Send to Specific Node
Rows Can Overflow to Temporary Tables
Subsection #4:
Data Stream 7:
Quote:
Not Piped
Access Table Queue ID = q4 #Columns = 327
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Residual Predicate(s)
| #Predicates = 4
Nested Loop Join
| Piped Inner
| (
| | Table Constructor
| | | 1-Row(s)
| | Residual Predicate(s)
| | | #Predicates = 3
| UNION
| | Table Constructor
| | | 1-Row(s)
| | Residual Predicate(s)
| | | #Predicates = 3
| )
Insert Into Temp Table ID = t7
| #Columns = 337
End of Data Stream 7
Access Temp Table ID = t7
Quote:
#Columns = 337
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q3
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q3

Subsection #5:
Access Table Name = TR_3.FACT ID = 14,5
Quote:
#Columns = 328
Compressed Table
Relation Scan
| Prefetch: Eligible
Isolation Level: Read Stability
Lock Intents
| Table: Intent Exclusive
| Row : Exclusive
Sargable Predicate(s)
| Process Build Table for Hash Join
Left Outer Hash Join
Early Out: Single Match Per Outer Row
Estimated Build Size: 1024000
Estimated Probe Size: 4000000
Access Table Queue ID = q5 #Columns = 322
Insert Into Sorted Temp Table ID = t8
#Columns = 327
#Sort Key Columns = 1
| Key 1: (Ascending)
Sortheap Allocation Parameters:
| #Rows = 1995.000000
| Row Width = 2448
Piped
Access Temp Table ID = t8
#Columns = 327
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| 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 #6:
Access Table Name = DMTRSTG.DOC_HDR ID = 14,6
Quote:
#Columns = 323
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| #Predicates = 1
Insert Into Asynchronous Table Queue ID = q5
Hash to Specific Node
Rows Can Overflow to Temporary Tables
End of section


Optimizer Plan:


Optimizer Plan:

INSERT
( 2)
/ \
TBSCAN Table:
( 3) TR_3
Quote:
FACT
TEMP
( 4)
Quote:
DTQ
( 5)
Quote:
TBSCAN
( 6)
Quote:
TEMP
( 7)
Quote:
DELETE
( 8)
/ \
FETCH Table:
( 9) TR_3
/ \ FACT
TBSCAN Table:
( 10) TR_3
Quote:
FACT
TEMP
( 11)
Quote:
UPDATE
( 12)
/ \
FETCH Table:
( 13) TR_3
/ \ FACT
TBSCAN Table:
( 14) TR_3
Quote:
FACT
TEMP
( 15)
Quote:
DTQ
( 16)
Quote:
TBSCAN
( 17)
Quote:
TEMP
( 18)
Quote:
INSERT
( 19)
/ \
TBSCAN Table:
( 20) TR_3
Quote:
FACT
TEMP
( 21)
Quote:
DTQ
( 22)
Quote:
TBSCAN
( 23)
Quote:
TEMP
( 24)
Quote:
NLJOIN
( 25)
/ \
FILTER UNION
( 26) ( 34)
Quote:
/ \
MDTQ FILTER FILTER
( 27) ( 35) ( 37)
Quote:
| |
TBSCAN TBSCAN TBSCAN
( 28) ( 36) ( 38)
Quote:
| |
SORT TFunc: TFunc:
( 29) SYSIBM SYSIBM
Quote:
GENROW GENROW
HSJOIN
( 30)
/ \
DTQ TBSCAN
( 31) ( 33)
Quote:
|
TBSCAN Table:
( 32) TR_3
Quote:
FACT
Table:
DMTRSTG
STAGING

Reply With Quote
  #12  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 12:52 PM





CREATE TABLE FACT (
BATCH_NUM INTEGER NOT NULL,
DRN BIGINT NOT NULL,
SOURCE_ID INTEGER NOT NULL,
ID BIGINT ,
REVIEW_STATUS_PPR_INVC_DT DATE,
REVIEW_STATUS_PPR_INVC_DT_IDN INTEGER,
REVIEW_STATUS_PAPER_INVC CHAR(2) ,
ACTL_PKUP_QUAL CHAR(3) ,
ACTL_PKUP_QUAL_DESC VARCHAR(50) ,
DOC_REF1_DT TIMESTAMP ,
DOC_REF1_DT_DESC VARCHAR(50) ,
DOC_REF1_DT_QUAL CHAR(3) ,
DOC_REF2_DT TIMESTAMP ,
DOC_REF2_DT_DESC VARCHAR(50) ,
DOC_REF2_DT_QUAL CHAR(3) ,
DOC_REF3_DT TIMESTAMP ,
DOC_REF3_DT_DESC VARCHAR(50) ,
DOC_REF3_DT_QUAL CHAR(3) ,
DOC_REF4_DT TIMESTAMP ,
DOC_REF4_DT_DESC VARCHAR(50) ,
DOC_REF4_DT_QUAL CHAR(3) ,
ADJ_INVC_CNT SMALLINT ,
ADJ_INVC_LNK_TO_THIS_DOC_IND CHAR(1) ,
ADJ_INVC_TTL_CHRG DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_NONVAT_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_VAT_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_LN_ITM_CHRG DECIMAL(19,4) ,
PREMIUM_TRANSPORTATION_IND CHAR(1) ,
ADJ_INVC_TTL_LN_TAX DECIMAL(19,4) ,
ZONE VARCHAR(4) ,
ADJ_INVC_TTL_LN_NONVAT_TAX DECIMAL(19,4) ,
HDR_MSG2 VARCHAR(1056) ,
HDR_MSG2_CREATE_DT TIMESTAMP ,
HDR_MSG3 VARCHAR(1056) ,
HDR_MSG3_CREATE_DT TIMESTAMP ,
MOVE_TY VARCHAR(99) ,
ADJ_INVC_TTL_LN_VAT_TAX DECIMAL(19,4) ,
NOTE2_COMPANY VARCHAR(140) ,
NOTE2_CREATEBY VARCHAR(47) ,
NOTE2_CREATE_DT TIMESTAMP ,
NOTE2_DESC VARCHAR(320) ,
NOTE2_LN_NBR VARCHAR(20) ,
NOTE3_COMPANY VARCHAR(140) ,
NOTE3_CREATEBY VARCHAR(47) ,
NOTE3_CREATE_DT TIMESTAMP ,
NOTE3_DESC VARCHAR(320) ,
NOTE3_LN_NBR VARCHAR(20) ,
NOTE3_LN_TY CHAR(2) ,
NOTE3_MSG VARCHAR(264) ,
NOTE2_LN_TY CHAR(2) ,
NOTE2_MSG VARCHAR(264) ,
OFF_CONTRACT_PRICE_IND VARCHAR(99) ,
ADJ_INVC_TTL_SC DECIMAL(19,4) ,
ADJ_INVC_TTL_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_NONVAT_TAX DECIMAL(19,4) ,
EQUIPMENT_TY VARCHAR(75) ,
INCO_TERMS VARCHAR(50) ,
INCO_TERMS_CD CHAR(3) ,
ADJ_INVC_TTL_VAT_TAX DECIMAL(19,4) ,
BILL_TO_ADDR_LN1 VARCHAR(220) ,
BILL_TO_ADDR_LN2 VARCHAR(220) ,
BILL_TO_CITY VARCHAR(30) ,
CONSOLIDATED_INVC_SHIP_IND CHAR(1) ,
UDF1_DESC VARCHAR(320) ,
UDF1_VAL VARCHAR(120) ,
UDF2_DESC VARCHAR(320) ,
UDF2_VAL VARCHAR(120) ,
UDF3_DESC VARCHAR(320) ,
UDF3_VAL VARCHAR(120) ,
REF4_DESC VARCHAR(80) ,
REF4_QUAL CHAR(3) ,
REF4_VAL VARCHAR(30) ,
REF5_DESC VARCHAR(80) ,
REF5_QUAL CHAR(3) ,
REF5_VAL VARCHAR(30) ,
REF6_DESC VARCHAR(80) ,
REF6_QUAL CHAR(3) ,
REF6_VAL VARCHAR(30) ,
REF7_DESC VARCHAR(80) ,
REF7_QUAL CHAR(3) ,
REF7_VAL VARCHAR(30) ,
BILL_TO_COUNTRY VARCHAR(50) ,
BILL_TO_ID VARCHAR(80) ,
DIRECTION VARCHAR(10) ,
BUSS_SEGMENT VARCHAR(99) ,
BILL_TO_ID_TY VARCHAR(50) ,
BILL_TO_ID_TY_QUAL CHAR(2) ,
BILL_TO_NM VARCHAR(240) ,
BILL_TO_POSTAL_CD VARCHAR(15) ,
BILL_TO_STATE VARCHAR(30) ,
BILL_TO_STATE_CD CHAR(2) ,
BILL_INCL_ADJ_INVC_CHRG_CWT DECIMAL(7,4) ,
BUYER_ADDNL1_ID VARCHAR(30) ,
BUYER_ADDNL1_ID_TY VARCHAR(50) ,
BUYER_ADDNL1_ID_TY_QUAL CHAR(2) ,
BUYER_ADDNL2_ID VARCHAR(30) ,
BUYER_ADDNL2_ID_TY VARCHAR(50) ,
BUYER_ADDNL2_ID_TY_QUAL CHAR(2) ,
BUYER_ADDNL3_ID VARCHAR(30) ,
BUYER_ADDNL3_ID_TY VARCHAR(50) ,
BUYER_ADDNL3_ID_TY_QUAL CHAR(2) ,
BUYER_COUNTRY VARCHAR(50) ,
BUYER_NM VARCHAR(240) ,
BUYER_POSTAL_CD VARCHAR(15) ,
BUYER_STATE VARCHAR(30) ,
BUYER_STATE_CD CHAR(2) ,

Reply With Quote
  #13  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 12:53 PM


REF_QTY1_UOM CHAR(5) ,
REF_QTY1_UOM_TY CHAR(3) ,
REF_QTY1_UOM_TY_DESC VARCHAR(30) ,
REF_QTY1_VAL DECIMAL(16,4) ,
REF_QTY2_UOM CHAR(5) ,
REF_QTY2_UOM_TY CHAR(3) ,
REF_QTY2_UOM_TY_DESC VARCHAR(30) ,
REF_QTY2_VAL DECIMAL(16,4) ,
REF_QTY3_UOM CHAR(5) ,
REF_QTY3_UOM_TY CHAR(3) ,
REF_QTY3_UOM_TY_DESC VARCHAR(30) ,
REF_QTY3_VAL DECIMAL(16,4) ,
REF_QTY4_UOM CHAR(5) ,
REF_QTY4_UOM_TY CHAR(3) ,
REF_QTY4_UOM_TY_DESC VARCHAR(30) ,
REF_QTY4_VAL DECIMAL(16,4) ,
CARR_ID VARCHAR(80) ,
CARR_ID_TY VARCHAR(50) ,
CARR_ID_TY_QUAL CHAR(2) ,
CONSIGNEE_ADDNL1_ID VARCHAR(30) ,
CONSIGNEE_ADDNL1_ID_TY VARCHAR(50) ,
CONSIGNEE_ADDNL1_ID_TY_QUAL CHAR(2) ,
CONSIGNEE_ADDR_LN1 VARCHAR(55) ,
CONSIGNEE_ADDR_LN2 VARCHAR(55) ,
CONSIGNEE_CITY VARCHAR(30) ,
CONSIGNEE_COUNTRY VARCHAR(50) ,
CONSIGNEE_ID VARCHAR(80) ,
CONSIGNEE_ID_TY VARCHAR(50) ,
CONSIGNEE_ID_TY_QUAL CHAR(2) ,
CONSIGNEE_NM VARCHAR(240) ,
CONSIGNEE_POSTAL_CD VARCHAR(15) ,
CONSIGNEE_STATE VARCHAR(30) ,
CONSIGNEE_STATE_CD CHAR(2) ,
CONTAINER_VOL DECIMAL(16,4) ,
DUE_DT_USED_IN_PAY_IND CHAR(1) ,
EXP_CWT DECIMAL(7,4) ,
INVC_TERMS INTEGER ,
MKT_DESC VARCHAR(30) ,
NOTICE1_STATUS VARCHAR(20) ,
NOTICE1_STATUS_COND VARCHAR(35) ,
NOTICE1_STATUS_EFF_DT TIMESTAMP ,
ORIGO_TO_FINI_LN_INVC_PRC DECIMAL(7,4) ,
ORIGO_TO_FINI_SC_INVC_PRC DECIMAL(7,4) ,
ORIGO_TO_FINI_TAX_PRC_INVC DECIMAL(7,4) ,
ORIGO_TO_FINI_TTL_PRC_INVC DECIMAL(7,4) ,
ORIG_ORD_TO_FIN_INVC_LN_VAR DECIMAL(19,4) ,
ORIG_ORD_TO_FIN_INVC_SC_VAR DECIMAL(19,4) ,
ORIG_ORD_TO_FIN_INVC_TAX_VAR DECIMAL(19,4) ,
ORIG_ORD_TO_FIN_INVC_TTL_VAR DECIMAL(19,4) ,
NOTICE1_STATUS_LOC VARCHAR(200) ,
NOTICE1_STATUS_POST_BY TIMESTAMP ,
NOTICE1_STATUS_RCV_BY VARCHAR(240) ,
NOTICE1_STATUS_TY VARCHAR(50) ,
ORD_SHIP_FROM_ADDNL1_IDTY_QUAL CHAR(2) ,
NOTICE2_STATUS VARCHAR(20) ,
NOTICE2_STATUS_COND VARCHAR(35) ,
ORD_SHIP_FROM_ADDNL2_IDTY_QUAL CHAR(2) ,
NOTICE2_STATUS_EFF_DT TIMESTAMP ,
NOTICE2_STATUS_LOC VARCHAR(200) ,
ORD_SHIP_FROM_ADDNL3_IDTY_QUAL CHAR(2) ,
NOTICE2_STATUS_POST_BY TIMESTAMP ,
NOTICE2_STATUS_RCV_BY VARCHAR(240) ,
NOTICE2_STATUS_TY VARCHAR(50) ,
NOTICE3_STATUS VARCHAR(20) ,
NOTICE3_STATUS_COND VARCHAR(35) ,
NOTICE3_STATUS_EFF_DT TIMESTAMP ,
NOTICE3_STATUS_LOC VARCHAR(200) ,
NOTICE3_STATUS_POST_BY TIMESTAMP ,
NOTICE3_STATUS_RCV_BY VARCHAR(240) ,
NOTICE3_STATUS_TY VARCHAR(50) ,
ORD_SHIP_FROM_ADDNL1_ID VARCHAR(30) ,
ORD_SHIP_FROM_ADDNL1_ID_TY VARCHAR(50) ,
ORD_SHIP_FROM_ADDNL2_ID VARCHAR(30) ,
ORD_SHIP_FROM_ADDNL2_ID_TY VARCHAR(50) ,
ORD_SHIP_FROM_ADDNL3_ID VARCHAR(30) ,
ORD_SHIP_FROM_ADDNL3_ID_TY VARCHAR(50) ,
ORD_SHIP_FROM_ADDR_LN1 VARCHAR(220) ,
ORD_SHIP_FROM_ADDR_LN2 VARCHAR(220) ,
ORD_SHIP_FROM_CITY VARCHAR(30) ,
ORD_SHIP_FROM_COUNTRY VARCHAR(50) ,
ORD_SHIP_FROM_COUNTRY_CD CHAR(3) ,
ORD_SHIP_FROM_COUNTY VARCHAR(30) ,
ORD_SHIP_FROM_FACILITY VARCHAR(30) ,
ORD_SHIP_FROM_ID VARCHAR(80) ,
ORD_SHIP_FROM_ID_TY VARCHAR(50) ,
ORG_ORD_TTL_TAX DECIMAL(19,4) ,
ORD_SHIP_FROM_ID_TY_QUAL CHAR(2) ,
ORD_SHIP_FROM_LOC_TY VARCHAR(30) ,
ORD_SHIP_FROM_LOC_TY_DESC VARCHAR(35) ,
ORD_SHIP_FROM_NAME VARCHAR(240) ,
ORD_SHIP_FROM_POSTAL_CD VARCHAR(15) ,
ORD_SHIP_FROM_STD_COUNTRY_CD CHAR(3) ,
ORD_SHIP_FROM_STATE VARCHAR(30) ,
ORD_SHIP_FROM_STATE_CD CHAR(2) ,
ORD_SHIP_TO_ADDNL1_ID VARCHAR(30) ,
ORD_SHIP_TO_ADDNL1_ID_TY VARCHAR(50) ,
ORD_SHIP_TO_ADDNL1_ID_TY_QUAL CHAR(2) ,
ORD_SHIP_TO_ADDNL2_ID VARCHAR(30) ,
ORD_SHIP_TO_ADDNL2_ID_TY VARCHAR(50) ,
ORD_SHIP_TO_ADDNL2_ID_TY_QUAL CHAR(2) ,
ORD_SHIP_TO_ADDNL3_ID VARCHAR(30) ,
ORD_SHIP_TO_ADDNL3_ID_TY VARCHAR(50) ,
ORD_SHIP_TO_ADDNL3_ID_TY_QUAL CHAR(2) ,
ORD_SHIP_TO_ADDR_LN1 VARCHAR(220) ,
ORD_SHIP_TO_ADDR_LN2 VARCHAR(220) ,
ORD_SHIP_TO_CITY VARCHAR(30) ,
ORD_SHIP_TO_COUNTRY VARCHAR(50) ,
ORD_SHIP_TO_COUNTRY_CD CHAR(3) ,
ORD_SHIP_TO_COUNTY VARCHAR(30) ,
ORD_SHIP_TO_FACILITY VARCHAR(30) ,
ORD_SHIP_TO_ID VARCHAR(80) ,
ORD_SHIP_TO_ID_TY VARCHAR(50) ,
ORD_TTL_HDR_NONVAT_TAX DECIMAL(19,4) ,
ORD_TTL_HDR_VAT_TAX DECIMAL(19,4) ,
ORD_TTL_LN_NONVAT_TAX DECIMAL(19,4) ,
ORD_TTL_LN_VAT_TAX DECIMAL(19,4) ,
ORD_SHIP_TO_ID_TY_QUAL CHAR(2) ,
ORD_SHIP_TO_LOC_TY VARCHAR(30) ,
ORD_SHIP_TO_LOC_TY_DESC VARCHAR(35) ,
ORD_SHIP_TO_NM VARCHAR(240) ,
ORD_SHIP_TO_POSTAL_CD VARCHAR(15) ,
ORD_SHIP_TO_STD_COUNTRY_CD CHAR(3) ,
ORD_SHIP_TO_STATE VARCHAR(30) ,
ORD_SHIP_TO_STATE_CD CHAR(2) ,

Reply With Quote
  #14  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 12:54 PM


.......................

)
DISTRIBUTE BY HASH(DRN,
SOURCE_ID)
IN TSM003 INDEX IN IXM003 ;

ALTER TABLE FACT VOLATILE CARDINALITY;


-- DDL Statements for primary key on Table FACT

ALTER TABLE FACT
ADD PRIMARY KEY
(DRN,
SOURCE_ID);

CREATE INDEX IDX01_DOC_HDR
ON DOC_HDR ( ID );

Reply With Quote
  #15  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 01:52 PM


Section Code Page = 1208

Estimated Cost = 9530.152344
Estimated Cardinality = 638.346680

Coordinator Subsection - Main Processing:
Distribute Subsection #6
Quote:
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 #3
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 = 1, 2, 3, 4, 5, 6, 7, 8
Subsection #1:
Data Stream 1:
Quote:
Not Piped
Access Table Queue ID = q1 #Columns = 331
Insert Into Temp Table ID = t1
| #Columns = 331
End of Data Stream 1
Access Temp Table ID = t1
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Insert: Table Name = TR_3.FACT ID = 14,5
Insert Predicate(s)
| #Predicates = 2
Subsection #2:
Data Stream 2:
Quote:
Not Piped
Data Stream 3:
| Not Piped
| Data Stream 4:
| | Not Piped
| | Access Table Queue ID = q2 #Columns = 333
| | Insert Into Temp Table ID = t2
| | | #Columns = 333
| End of Data Stream 4
| Access Temp Table ID = t2
| | #Columns = 333
| | Relation Scan
| | | Prefetch: Eligible
| Establish Row Position
| | Access Table Name = TR_3.FACT ID = 14,5
| Update: Table Name = TR_3.FACT ID = 14,5
| | Update Predicate(s)
| | | #Predicates = 2
| Insert Into Temp Table ID = t3
| | #Columns = 333
End of Data Stream 3
Access Temp Table ID = t3
| #Columns = 333
| Relation Scan
| | Prefetch: Eligible
Establish Row Position
| Access Table Name = TR_3.FACT ID = 14,5
Delete: Table Name = TR_3.FACT ID = 14,5
| Delete Predicate(s)
| | #Predicates = 2
Insert Into Temp Table ID = t4
| #Columns = 331
End of Data Stream 2
Access Temp Table ID = t4
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q1
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q1

Subsection #3:
Data Stream 5:
Quote:
Not Piped
Data Stream 6:
| Not Piped
| Access Table Queue ID = q3 #Columns = 337
| Insert Into Temp Table ID = t5
| | #Columns = 337
End of Data Stream 6
Access Temp Table ID = t5
| #Columns = 337
| Relation Scan
| | Prefetch: Eligible
Insert: Table Name = TR_3.FACT ID = 14,5
| Insert Predicate(s)
| | #Predicates = 1
Insert Into Temp Table ID = t6
| #Columns = 333
End of Data Stream 5
Access Temp Table ID = t6
Quote:
#Columns = 333
Relation Scan
| Prefetch: Eligible
Insert Into Asynchronous Table Queue ID = q2
Send to Specific Node
Rows Can Overflow to Temporary Tables
Subsection #4:
Data Stream 7:
Quote:
Not Piped
Access Table Queue ID = q4 #Columns = 327
| Output Sorted
| | #Key Columns = 1
| | | Key 1: (Ascending)
Residual Predicate(s)
| #Predicates = 4
Nested Loop Join
| Piped Inner
| (
| | Table Constructor
| | | 1-Row(s)
| | Residual Predicate(s)
| | | #Predicates = 3
| UNION
| | Table Constructor
| | | 1-Row(s)
| | Residual Predicate(s)
| | | #Predicates = 3
| )
Insert Into Temp Table ID = t7
| #Columns = 337
End of Data Stream 7
Access Temp Table ID = t7
Quote:
#Columns = 337
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q3
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q3

Subsection #5:
Access Table Name = TR_3.FACT ID = 14,5
Quote:
#Columns = 328
Compressed Table
Relation Scan
| Prefetch: Eligible
Isolation Level: Read Stability
Lock Intents
| Table: Intent Exclusive
| Row : Exclusive
Sargable Predicate(s)
| Process Build Table for Hash Join
Left Outer Hash Join
Early Out: Single Match Per Outer Row
Estimated Build Size: 1024000
Estimated Probe Size: 4000000
Access Table Queue ID = q5 #Columns = 322
Insert Into Sorted Temp Table ID = t8
#Columns = 327
#Sort Key Columns = 1
| Key 1: (Ascending)
Sortheap Allocation Parameters:
| #Rows = 1995.000000
| Row Width = 2448
Piped
Access Temp Table ID = t8
#Columns = 327
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| 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 #6:
Access Table Name = DMTRSTG.STAGING ID = 14,6
Quote:
#Columns = 323
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| #Predicates = 1
Insert Into Asynchronous Table Queue ID = q5
Hash to Specific Node
Rows Can Overflow to Temporary Tables
End of section


Optimizer Plan:


Optimizer Plan:

INSERT
( 2)
/ \
TBSCAN Table:
( 3) TR_3
Quote:
FACT
TEMP
( 4)
Quote:
DTQ
( 5)
Quote:
TBSCAN
( 6)
Quote:
TEMP
( 7)
Quote:
DELETE
( 8)
/ \
FETCH Table:
( 9) TR_3
/ \ FACT
TBSCAN Table:
( 10) TR_3
Quote:
FACT
TEMP
( 11)
Quote:
UPDATE
( 12)
/ \
FETCH Table:
( 13) TR_3
/ \ FACT
TBSCAN Table:
( 14) TR_3
Quote:
FACT
TEMP
( 15)
Quote:
DTQ
( 16)
Quote:
TBSCAN
( 17)
Quote:
TEMP
( 18)
Quote:
INSERT
( 19)
/ \
TBSCAN Table:
( 20) TR_3
Quote:
FACT
TEMP
( 21)
Quote:
DTQ
( 22)
Quote:
TBSCAN
( 23)
Quote:
TEMP
( 24)
Quote:
NLJOIN
( 25)
/ \
FILTER UNION
( 26) ( 34)
Quote:
/ \
MDTQ FILTER FILTER
( 27) ( 35) ( 37)
Quote:
| |
TBSCAN TBSCAN TBSCAN
( 28) ( 36) ( 38)
Quote:
| |
SORT TFunc: TFunc:
( 29) SYSIBM SYSIBM
Quote:
GENROW GENROW
HSJOIN
( 30)
/ \
DTQ TBSCAN
( 31) ( 33)
Quote:
|
TBSCAN Table:
( 32) TR_3
Quote:
FACT
Table:
DMTRSTG
STAGING

Reply With Quote
  #16  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 02:27 PM


I split into 2 statement.
The target table is empty, Merge insert is OK.
Slow part is merge update, 10 times than insert although nothing to
update.

Reply With Quote
  #17  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 02:40 PM


split into 2 statement.
The target table is empty, Merge insert is OK.
Slow part is merge update, 10 times than insert although nothing to
update.

From the plan show MERGE UPDATE is scanning the Target table, but
MERGE INSERT is using Primary key.


DB2 v9.1.0.7

Reply With Quote
  #18  
Old   
Anwei Shen
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-09-2011 , 03:42 PM


MERGE INTO FACT
USING STAGING
WHEN MATCHED
THEN
UPDATE SET (... ) = (...)


Section Code Page = 1208

Estimated Cost = 64481.765625
Estimated Cardinality = 5745.120117

Coordinator Subsection - Main Processing:
Distribute Subsection #5
Quote:
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 #3
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 = 1, 2, 3, 4, 5, 6, 7, 8
Subsection #1:
Data Stream 1:
Quote:
Not Piped
Access Table Queue ID = q1 #Columns = 331
Insert Into Temp Table ID = t1
| #Columns = 331
End of Data Stream 1
Access Temp Table ID = t1
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Insert: Table Name = FACT ID = 14,5
Insert Predicate(s)
| #Predicates = 2
Subsection #2:
Data Stream 2:
Quote:
Not Piped
Access Table Queue ID = q2 #Columns = 333
Establish Row Position
| Access Table Name = FACT ID = 14,5
Update: Table Name = FACT ID = 14,5
| Update Predicate(s)
| | #Predicates = 2
Delete: Table Name = FACT ID = 14,5
| Delete Predicate(s)
| | #Predicates = 2
Insert Into Temp Table ID = t2
| #Columns = 331
End of Data Stream 2
Access Temp Table ID = t2
Quote:
#Columns = 331
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q1
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q1

Subsection #3:
Access Table Queue ID = q3 #Columns = 327
Quote:
Output Sorted
| #Key Columns = 1
| | Key 1: (Ascending)
Residual Predicate(s)
#Predicates = 4
Residual Predicate(s)
#Predicates = 1
Insert Into Asynchronous Table Queue ID = q2
Send to Specific Node
Rows Can Overflow to Temporary Tables
Subsection #4:
Access Table Name = FACT ID = 14,5
Quote:
#Columns = 328
Compressed Table
Relation Scan
| Prefetch: Eligible
Isolation Level: Read Stability
Lock Intents
| Table: Intent Exclusive
| Row : Exclusive
Sargable Predicate(s)
| Process Build Table for Hash Join
Left Outer Hash Join
Early Out: Single Match Per Outer Row
Estimated Build Size: 224000
Estimated Probe Size: 4000000
Access Table Queue ID = q4 #Columns = 322
Insert Into Sorted Temp Table ID = t3
#Columns = 327
#Sort Key Columns = 1
| Key 1: (Ascending)
Sortheap Allocation Parameters:
| #Rows = 1995.000000
| Row Width = 2448
Piped
Access Temp Table ID = t3
#Columns = 327
Relation Scan
| Prefetch: Eligible
Sargable Predicate(s)
| Insert Into Asynchronous Table Queue ID = q3
| | Hash to Specific Node
| | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q3

Subsection #5:
Access Table Name = STAGING. ID = 14,6
Quote:
#Columns = 323
Volatile Cardinality
Relation Scan
| Prefetch: Eligible
Lock Intents
| Table: Intent Share
| Row : Next Key Share
Sargable Predicate(s)
| #Predicates = 1
Insert Into Asynchronous Table Queue ID = q4
Hash to Specific Node
Rows Can Overflow to Temporary Tables
End of section


Optimizer Plan:

INSERT
( 2)
/ \
TBSCAN Table:
( 3) FACT
Quote:
TEMP
( 4)
Quote:
DTQ
( 5)
Quote:
TBSCAN
( 6)
Quote:
TEMP
( 7)
Quote:
DELETE
( 8)
/--/ \
UPDATE Table:
( 9) FACT
/ \
FETCH Table:
( 10) FACT
/ \
DTQ Table:
( 11) FACT
Quote:
FILTER
( 12)
Quote:
FILTER
( 13)
Quote:
MDTQ
( 14)
Quote:
TBSCAN
( 15)
Quote:
SORT
( 16)
Quote:
HSJOIN
( 17)
/ \
DTQ TBSCAN
( 18) ( 20)
Quote:
|
TBSCAN Table:
( 19) FACT
Quote:
Table:
STAGING

Reply With Quote
  #19  
Old   
Serge Rielau
 
Posts: n/a

Default Re: DB2 MERGE STATEMENT ARE using table scan - 03-11-2011 , 02:45 PM


Quote:
INSERT
( 2)
/ \
TBSCAN Table:
( 3) FACT
|
TEMP
( 4)
|
DTQ
( 5)
|
TBSCAN
( 6)
|
TEMP
( 7)
|
DELETE
( 8)
/--/ \
UPDATE Table:
( 9) FACT
/ \
FETCH Table:
( 10) FACT
/ \
DTQ Table:
( 11) FACT
|
These two filters area sign that DB2 cannot prove that
you won't have two rows in the source matching the same row in the
target. So DB2 will do extra work to catch duplicates.
Ideally you should have a unique index on the columns in the ON clause.
Quote:
FILTER
( 12)
|
FILTER
( 13)
|
MDTQ
( 14)
|
TBSCAN
( 15)
|
SORT
( 16)
|
Your staging table's partitioning should match the FACT table's
partitioning.
That will eliminate the DTQ(18)
Quote:
HSJOIN
( 17)
/ \
DTQ TBSCAN
( 18) ( 20)
| |
TBSCAN Table:
( 19) FACT
|
Table:
STAGING

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top