explain 사용법

explain 사용법

작성한 SQL문이 어떤 ACCESS PATH를 가지는지 SQL문을 수해하기에 확인을 해야 합니다. 이에 대한 사용방법을 기술합니다..

Explain utility

Explain을 볼 수 있는 utility로 db2expln, dynexpln, explain table을 활용한 db2exfmt, visual explain등 이 있습니다.

• Visual Explain과 db2exfmt은 explain table을 생성해야 사용할 수 있으며 explain table 생성 DDL문은 db2 instance home directory 아래 ./sqllib/misc/EXPLAIN.ddl file에 있습니다.
• Visual explain은 control center를 통해 볼 수 있습니다.
• Explain table이 없는 경우 db2expln을 사용하여 access path를 손쉽게 확인 할 수 있습니다. db2explain tool은 static및 dynamic sql문 모두 explain 가능하며 dynexpln은 dynamic sql문에 대한 explain만 가능합니다.

Explain 결과 보기

다음은 dynamic sql 문에 대한 explain 결과입니다. -f option이 의미하는 것은 sql문이 포함된 file명입니다. -o 는 explain output file 그리고 -z 는 SQL문 termination character입니다.

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** DYNAMIC ***************************************

==================== 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", "TIDINST"

SQL Statement:

SELECT T2.INIT_YMD, t1.MIGRATE_BAS_YMD, t1.UPDATE_YMD
FROM plt.ev T1 inner join PLT.AR_X_EV T2 on T1.ev_ID =T2.ev_ID
group by t2.init_Ymd, t1.MIGRATE_BAS_YMD, t1.UPDATE_YMD
WITH UR

Statement Isolation Level = Uncommitted Read

Section Code Page = 970

Estimated Cost = 3630835.250000
Estimated Cardinality = 44109824.000000

Coordinator Subsection - Main Processing:
(-----) Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
(-----) Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
(-----) Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
( 2) Access Table Queue ID = q1 #Columns = 3
( 1) Return Data to Application
| #Columns = 3

Subsection #1:
( 6) Access Table Queue ID = q2 #Columns = 3
( 5) Insert Into Sorted Temp Table ID = t1
| #Columns = 3
| #Sort Key Columns = 3
| | Key 1: (Ascending)
| | Key 2: (Ascending)
| | Key 3: (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 18769220
| | Row Width = 16
| Piped
( 4) Access Temp Table ID = t1
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
( 3) Final Aggregation
| Group By
( 2) Insert Into Asynchronous Table Queue ID = q1
| Broadcast to Coordinator Node
| Rows Can Overflow to Temporary Table

Subsection #2:
( 12) Access Table Queue ID = q3 #Columns = 2
( 10) Hash Join
| Estimated Build Size: 984675712
| Estimated Probe Size: 1293456000
| Bit Filter Size: 11286792
( 11) | Access Table Name = PLT.EV ID = 3,6
| | #Columns = 3
| | Relation Scan
| | | Prefetch: Eligible
| | Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Predicate(s)
( 11) | | | Process Probe Table for Hash Join
( 9) Insert Into Sorted Temp Table ID = t2
| #Columns = 3
| #Sort Key Columns = 3
| | Key 1: (Ascending)
| | Key 2: MIGRATE_BAS_YMD (Ascending)
| | Key 3: UPDATE_YMD (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 24321392
| | Row Width = 16
| Piped
( 8) Access Temp Table ID = t2
| #Columns = 3
| Relation Scan
| | Prefetch: Eligible
| Sargable Predicate(s)
( 8) | | Partial Predicate Aggregation
| | | Group By
( 7) Partial Aggregation Completion
| Group By
( 6) Insert Into Asynchronous Table Queue ID = q2
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #3:
( 13) Access Table Name = PLT.AR_X_EV ID = 3,5
| Index Scan: Name = PLT.ARXEV_PK ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: AR_ID (Ascending)
| | | 2: EV_ID (Ascending)
| | | 3: AR_X_EV_RELTN_CD (Ascending)
| | | 4: INIT_YMD (Ascending)
| | | 5: SEQ (Ascending)
| #Columns = 2
| #Key Columns = 0
| | Start Key: Beginning of Index
| | Stop Key: End of Index
| Index-Only Access
| Index Prefetch: Eligible 137410
| Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
( 13) | | Insert Into Asynchronous Table Queue ID = q3
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
( 12) Insert Into Asynchronous Table Queue Completion ID = q3

End of section

l 다음은 embeded sql문에 대한 explain 결과입니다. C로 embedded static SQL문을 작성한 경우 DB에 binding을 하게되면 package가 생성됩니다. db2expln tool을 사용하여 해당 package의 explain을 볼 수 있습니다. 아래 command에서 -c는 package의 schema를 의미하며 -p는 package의 이름입니다. explain결과는 -o option 다음에 기술합니다.
db2expln -d edwid_t -c titgusr -p pattern1 -o pattern1.expln

DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991, 2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool

******************** PACKAGE ***************************************

Package Name = "TITGUSR"."PATTERN1" Version = ""

Prep Date = 2004/03/02
Prep Time = 17:42:30

Bind Timestamp = 2004-03-02-17.43.07.942462

Isolation Level = Uncommitted Read
Blocking = Block All Cursors
Query Optimization Class = 5

Partition Parallel = Yes
Intra-Partition Parallel = No

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

-------------------- SECTION ---------------------------------------
Section = 1

SQL Statement:

SELECT substr(char(CURRENT TIMESTAMP), 1, 19) INTO :H00001
FROM SYSIBM.SYSDUMMY1

Section Code Page = 970

Estimated Cost = 0.000565
Estimated Cardinality = 1.000000

Coordinator Subsection - Main Processing:
Table Constructor
| 1-Row(s)
Return Data to Application
| #Columns = 1

End of section

-------------------- SECTION --------------------------------------
Section = 2

SQL Statement:

SELECT substr(char(CURRENT TIMESTAMP), 1, 19) INTO :H00002
FROM SYSIBM.SYSDUMMY1

Section Code Page = 970

Estimated Cost = 0.000565
Estimated Cardinality = 1.000000

Coordinator Subsection - Main Processing:
Table Constructor
| 1-Row(s)
Return Data to Application
| #Columns = 1

End of section

-------------------- SECTION ---------------------------------------
Section = 4

SQL Statement:

INSERT INTO PLTITG.TEST_COM ( EV_ID, FEE_ITEM_CD, FEE_ITEM_TYPE_CD,
TAX_CD, AMT, CUR_MONTH_AMT, DISCNT_AMT, ETC_FEE, CHRG_AMT,
CHRG_CYCL_CD, CHRG_CYCL_SNUM, CHRG_YMD,
NEXT_MONTH_INC_DECN_AMT, OVERD_FEE, PREPAY_AMT,
DECN_MONEY_FEE, SVC_CNT, USG_CNT, USG_UNIT_CNT,
CHRG_TYPE_CD, USG_UNIT_CD, BIND_SVC_NO, IA_ID, ORG_SA_ID,
SA_ID, SA_DTL_CD, MIGRATE_BAS_YMD, MIGRAT_WORK_DATE)
SELECT C.EV_ID,
CASE
WHEN S.CHRG_ITEM_CD IS NULL
THEN '_'
ELSE VALUE(A.COL4, '####') END,
CASE
WHEN S.CHRG_ITEM_TYPE_CD IS NULL
THEN '_'
ELSE VALUE(B.COL4, '##') END,
CASE
WHEN S.TAX_CD IS NULL
THEN '_'
ELSE VALUE(C.COL4, '##') END, S.AMT AMT, S.C_MONTH_AMT
CURR_MONTH_AMT, S.DC_AMT DISCNT_AMT, S.ETC_CHRG ETC_FEE,
S.INV_AMT CHRG_AMT,
case
WHEN S.INV_CYCLE_CD IS NULL
THEN '_'
ELSE VALUE(D.COL4, '##') END, S.INV_CYCLE_SEQ_NO CHRG_CYCL_SNUM,
S.INV_DATE CHRG_YMD, S.N_MONTH_ADJUST_AMT
NEXT_MONTH_INC_DECN_AMT, S.OVERDUE_CHRG OVERD_FEE,
S.PREPAY_AMT PREPAY_AMT, S.REDUCE_CHRG DECN_MONEY_FEE,
S.SVC_CNT SVC_CNT, S.USE_CNT USG_CNT, S.USE_UNIT_CNT
USG_UNIT_CNT,
case
WHEN S.INV_TYPE IS NULL
THEN '_'
ELSE VALUE(E.COL4, '##') END,
case
WHEN S.USE_UNIT_CD IS NULL
THEN '_'
ELSE VALUE(F.COL4, '##') END, S.NS_SVC_NO BIND_SVC_NO, S.IA_ID
IA_ID, S.ORG_SA_ID, S.SA_ID, S.SA_DTL_CD, CURRENT DATE,
CURRENT TIMESTAMP
FROM PLTITG.TEST_KEY_MAP C, PLTSTAG.TB_BIVKCICHRG S LEFT OUTER
JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.C.TB_BIVRCHRGITEM.CHARGE_ITEM_CD') A ON
S.CHRG_ITEM_CD =A.COL2 LEFT OUTER JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.G.BSYSCODE.CHRG_ITEM_TYPE_CD') B ON
S.CHRG_ITEM_TYPE_CD =B.COL2 LEFT OUTER JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.C.TB_BIVRTAX.TAX_CD') C ON S.TAX_CD =
C.COL2 LEFT OUTER JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.C.TB_BIVRBCYCLE.INV_CYCLE_CD' AND
COL3='0092') D ON S.INV_CYCLE_CD =D.COL2
LEFT OUTER JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.G.TB_BSYSCODE.INV_TYPE_CD' AND COL3=
'0093') E ON S.INV_TYPE =E.COL2 LEFT OUTER
JOIN (
SELECT COL2, COL4
FROM PLTITG.CMAP
WHERE COL1 ='02.G.TB_BSYSCODE.MEASURE_CD' AND COL3=
'0098') F ON S.USE_UNIT_CD =F.COL2
WHERE C.INV_DATE =S.INV_DATE AND C.IA_ID =S.IA_ID
AND C.SA_ID =S.SA_ID AND C.SA_DTL_CD =
S.SA_DTL_CD AND C.CHRG_ITEM_CD =
S.CHRG_ITEM_CD AND C.ORG_SA_ID =S.ORG_SA_ID

Section Code Page = 970

Estimated Cost = 1121.004517
Estimated Cardinality = 3.880000

Coordinator Subsection - Main Processing:
Distribute Subsection #4
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #5
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #8
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #7
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #6
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #2
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120
Distribute Subsection #1
| Broadcast to Node List
| | Nodes = 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
| | 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
| | 21, 22, 23, 24, 33, 34, 35, 36, 37, 38,
| | 39, 40, 41, 42, 43, 44, 45, 46, 47, 48,
| | 49, 50, 51, 52, 53, 54, 55, 56, 65, 66,
| | 67, 68, 69, 70, 71, 72, 73, 74, 75, 76,
| | 77, 78, 79, 80, 81, 82, 83, 84, 85, 86,
| | 87, 88, 97, 98, 99, 100, 101, 102, 103, 104,
| | 105, 106, 107, 108, 109, 110, 111, 112, 113, 114,
| | 115, 116, 117, 118, 119, 120

Subsection #1:
Access Table Queue ID = q1 #Columns = 28
Insert: Table Name = PLTITG.TEST_COM ID = 3,18

Subsection #2:
Access Table Name = PLTITG.CMAP ID = 3,23
| Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: COL1 (Ascending)
| | | 2: COL2 (Ascending)
| | | 3: COL3 (Ascending)
| #Columns = 4
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '02.G.TB_BSYSCODE.MEA...'
| | Stop Key: Inclusive Value
| | | | 1: '02.G.TB_BSYSCODE.MEA...'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | #Predicates = 1
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Estimated Build Size: 4000
| Estimated Probe Size: 80899
| Access Table Queue ID = q2 #Columns = 31
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 = 2
Left Outer Hash Join
| Estimated Build Size: 4026
| Estimated Probe Size: 52703
| Access Table Queue ID = q4 #Columns = 2
| Left Outer Hash Join
| | Estimated Build Size: 4026
| | Estimated Probe Size: 44887
| | Access Table Name = PLTITG.CMAP ID = 3,23
| | | Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: COL1 (Ascending)
| | | | | 2: COL2 (Ascending)
| | | | | 3: COL3 (Ascending)
| | | #Columns = 3
| | | #Key Columns = 1
| | | | Start Key: Inclusive Value
| | | | | | 1: '02.C.TB_BIVRTAX.TAX_...'
| | | | Stop Key: Inclusive Value
| | | | | | 1: '02.C.TB_BIVRTAX.TAX_...'
| | | Data Prefetch: None
| | | Index Prefetch: None
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Predicate(s)
| | | | Process Build Table for Hash Join
| | Left Outer Hash Join
| | | Estimated Build Size: 4047
| | | Estimated Probe Size: 37371
| | | Access Table Queue ID = q5 #Columns = 28
Insert Into Asynchronous Table Queue ID = q2
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #4:
Access Table Name = PLTITG.CMAP ID = 3,23
| Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: COL1 (Ascending)
| | | 2: COL2 (Ascending)
| | | 3: COL3 (Ascending)
| #Columns = 4
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '02.G.TB_BSYSCODE.INV...'
| | Stop Key: Inclusive Value
| | | | 1: '02.G.TB_BSYSCODE.INV...'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | #Predicates = 1
| 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 Name = PLTITG.CMAP ID = 3,23
| Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: COL1 (Ascending)
| | | 2: COL2 (Ascending)
| | | 3: COL3 (Ascending)
| #Columns = 4
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '02.C.TB_BIVRBCYCLE.I...'
| | Stop Key: Inclusive Value
| | | | 1: '02.C.TB_BIVRBCYCLE.I...'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | #Predicates = 1
| Sargable Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q4
| | | Broadcast to All Nodes of Subsection 3
| | | Rows Can Overflow to Temporary Table
Insert Into Asynchronous Table Queue Completion ID = q4

Subsection #6:
Access Table Name = PLTITG.CMAP ID = 3,23
| Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: COL1 (Ascending)
| | | 2: COL2 (Ascending)
| | | 3: COL3 (Ascending)
| #Columns = 3
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '02.G.BSYSCODE.CHRG_I...'
| | Stop Key: Inclusive Value
| | | | 1: '02.G.BSYSCODE.CHRG_I...'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Estimated Build Size: 4047
| Estimated Probe Size: 29554
| Access Table Queue ID = q6 #Columns = 27
Insert Into Asynchronous Table Queue ID = q5
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #7:
Access Table Name = PLTITG.CMAP ID = 3,23
| Index Scan: Name = PLTITG.CMAP_X1 ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: COL1 (Ascending)
| | | 2: COL2 (Ascending)
| | | 3: COL3 (Ascending)
| #Columns = 3
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: '02.C.TB_BIVRCHRGITEM...'
| | Stop Key: Inclusive Value
| | | | 1: '02.C.TB_BIVRCHRGITEM...'
| Data Prefetch: None
| Index Prefetch: None
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Left Outer Hash Join
| Estimated Build Size: 4047
| Estimated Probe Size: 5983
| Access Table Queue ID = q7 #Columns = 26
Insert Into Asynchronous Table Queue ID = q6
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

Subsection #8:
Access Table Name = PLTITG.TEST_KEY_MAP ID = 3,20
| #Columns = 7
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 7
| | | #Sort Key Columns = 1
| | | | Key 1: INV_DATE (Ascending)
| | | Sortheap Allocation Parameters:
| | | | #Rows = 97
| | | | Row Width = 68
| | | Piped
Sorted Temp Table Completion ID = t1
Access Temp Table ID = t1
| #Columns = 7
| Relation Scan
| | Prefetch: Eligible
Nested Loop Join
| Access Table Name = PLTSTAG.TB_BIVKCICHRG ID = 3,13
| | Index Scan: Name = PLTSTAG.BIVKCICHRG_PK ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: INV_DATE (Ascending)
| | | | 2: IA_ID (Ascending)
| | | | 3: SA_ID (Ascending)
| | | | 4: SA_DTL_CD (Ascending)
| | | | 5: CHRG_ITEM_CD (Ascending)
| | | | 6: ORG_SA_ID (Ascending)
| | #Columns = 25
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 6
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | | | 3: ?
| | | | | 4: ?
| | | | | 5: ?
| | | | | 6: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | | | | 2: ?
| | | | | 3: ?
| | | | | 4: ?
| | | | | 5: ?
| | | | | 6: ?
| | Data Prefetch: None
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent None
| | | Row : None
Insert Into Asynchronous Table Queue ID = q7
| Hash to Specific Node
| Rows Can Overflow to Temporary Tables

End of section