| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
06-27-11, 06:32
|
|
Registered User
|
|
Join Date: Jun 2011
Posts: 6
|
|
db2explain(db2explan)
|
|
Could Please Help on How to Analyze this One.
==================== 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", "SYSIBMADM",
"U03SWDB1"
Statement:
SELECT WHO.BIL_DIV_NO, WHO.STO_NO, WHO.DPT_NO, WHO.WHS_NO,
WHO.WHS_ORD_NO, WHO.ORD_DT, WHO.ORD_CST_AM, WHO.SAT_DT,
DPT.DPT_NAM_TX, ETY.ETY_NO, ETY.ETY_TYP_CD, ETY.STO_CRT_FL,
ETY.ICD_FL, ETY.CRE_INE_CD, ETY.RAS_UPD_ID, ETY.INV_NO,
ETY.ETY_DSC_TX, ETY.RAS_UPD_TS, ETY.SRC_DOC_NO
FROM PROD.DEPARTMENT DPT, PROD.WAREHOUSE_ORDER WHO LEFT JOIN (
SELECT ETY_NO, ETY_TYP_CD, STO_CRT_FL, ICD_FL, CRE_INE_CD,
ETY_DSC_TX, IE.RAS_UPD_ID AS RAS_UPD_ID, IE.RAS_UPD_TS
AS RAS_UPD_TS, INV.INV_NO AS INV_NO, SRC_DOC_NO,
BIL_DIV_NO, STO_NO, DPT_NO, ETY_DT
FROM PROD.INVENTORY INV, PROD.INVENTORY_ENTRY IE
WHERE INV.INV_NO =IE.INV_NO)AS ETY ON ETY.BIL_DIV_NO =
WHO.BIL_DIV_NO AND ETY.STO_NO =WHO.STO_NO AND ETY.DPT_NO =
WHO.DPT_NO AND ETY.ETY_TYP_CD ='AWF' AND ETY.ICD_FL ='1'
AND ETY.SRC_DOC_NO =CONCAT(WHO.WHS_NO, WHO.WHS_ORD_NO)AND
ETY.ETY_DT =WHO.ORD_DT
WHERE DPT.DPT_NO =WHO.DPT_NO AND DPT.BIL_DIV_NO =WHO.BIL_DIV_NO AND
DPT.BIL_DIV_NO ='011' AND WHO.STO_NO ='00245' AND
WHO.SAT_DT ='2011-03-05'
ORDER BY WHO.ORD_DT, WHO.DPT_NO, WHO.WHS_ORD_NO, WHO.WHS_NO,
WHO.ORD_CST_AM
WITH UR
Statement Isolation Level = Uncommitted Read
Section Code Page = 819
Estimated Cost = 1000454.375000
Estimated Cardinality = 169.416229
Access Table Name = PROD.WAREHOUSE_ORDER ID = 16,2
| Index Scan: Name = PROD.PK_WAREHOUSE_ORDER ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: BIL_DIV_NO (Ascending)
| | | 2: STO_NO (Ascending)
| | | 3: DPT_NO (Ascending)
| | | 4: WHS_NO (Ascending)
| | | 5: WHS_ORD_NO (Ascending)
| | | 6: ORD_DT (Ascending)
| #Columns = 0
| #Key Columns = 2
| | Start Key: Inclusive Value
| | | | 1: '011'
| | | | 2: '00245'
| | Stop Key: Inclusive Value
| | | | 1: '011'
| | | | 2: '00245'
| Index-Only Access
| Index Prefetch: Eligible 649
Isolation Level: Uncommitted Read
| Lock Intents
| | Table: Intent None
| | Row : None
| Sargable Index Predicate(s)
| | Insert Into Sorted Temp Table ID = t1
| | | #Columns = 1
| | | #Sort Key Columns = 1
| | | | Key 1: (Ascending)
| | | Sortheap Allocation Parameters:| | | | #Rows = 64972.000000
| | | | Row Width = 16
| | | Piped
| | | Duplicate Elimination
Sorted Temp Table Completion ID = t1
List Prefetch Preparation
| Access Table Name = PROD.WAREHOUSE_ORDER ID = 16,2
| | #Columns = 6
| | Fetch Using Prefetched List
| | | Prefetch: 38025 Pages
Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Predicate(s)
| | | #Predicates = 3
| | | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Inner Row
| Estimated Build Size: 16000
| Estimated Probe Size: 8000
| Access Table Name = PROD.DEPARTMENT ID = 7,2
| | Index Scan: Name = PROD.DEPARTMENTINDIVISI ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: BIL_DIV_NO (Ascending)
| | #Columns = 3
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: '011'
| | | Stop Key: Inclusive Value
| | | | | 1: '011'
| | Data Prefetch: None
| | Index Prefetch: None
Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Insert Into Sorted Temp Table ID = t2
| #Columns = 6
| #Sort Key Columns = 4
| | Key 2: DPT_NO (Ascending)
| | Key 3: WHS_ORD_NO (Ascending)
| | Key 4: WHS_NO (Ascending)
| Sortheap Allocation Parameters:
| | #Rows = 170.000000
| | Row Width = 76
| Piped
Access Temp Table ID = t2
| #Columns = 6
| Relation Scan
| | Prefetch: Eligible
Left Outer Merge Join
| Access Table Name = PROD.INVENTORY_ENTRY ID = 12,2
| | Index Scan: Name = PROD.IX_14_WIZ4554 ID = 7
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: ETY_TYP_CD (Ascending)
| | | | 2: ICD_FL (Ascending)
| | | | 3: ETY_DT (Ascending)
| | | | 4: SRC_DOC_NO (Ascending)
| | | | 5: RAS_UPD_TS (Ascending)
| | | | 6: RAS_UPD_ID (Ascending)
| | | | 7: ETY_DSC_TX (Ascending)
| | | | 8: CRE_INE_CD (Ascending)
| | | | 9: STO_CRT_FL (Ascending)
| | | | 10: ETY_NO (Ascending)
| | | | 11: INV_NO (Ascending)
| | #Columns = 9
| | #Key Columns = 2
| | | Start Key: Inclusive Value
| | | | | 1: 'AWF'
| | | | | 2: '1'
| | | Stop Key: Inclusive Value
| | | | | 1: 'AWF'
| | | | | 2: '1'
| | Index-Only Access
| | Index Prefetch: Eligible 2590
Isolation Level: Uncommitted Read
| | Lock Intents
| | | Table: Intent None
| | | Row : None
| | Sargable Index Predicate(s)
| | | Process Build Table for Hash Join
| | Early Out: Single Match Per Inner Row
| | Estimated Build Size: 11856000
| | Estimated Probe Size: 30448000
| | Access Table Name = PROD.INVENTORY ID = 11,2
| | | Index Scan: Name = PROD.IX_2_WIZ5158 ID = 4
| | | | Regular Index (Not Clustered)
| | | | Index Columns:
| | | | | 1: INV_DT (Ascending)
| | | | | 2: STO_NO (Ascending)
| | | | | 3: BIL_DIV_NO (Ascending)
| | | | | 4: DPT_NO (Ascending)
| | | | | 5: INV_NO (Ascending)
| | | #Columns = 4
| | | #Key Columns = 0
| | | | Start Key: Beginning of Index
| | | | Stop Key: End of Index
| | | Index-Only Access
| | | Index Prefetch: Eligible 8463
Isolation Level: Uncommitted Read
| | | Lock Intents
| | | | Table: Intent None
| | | | Row : None
| | | Sargable Index Predicate(s)
| | | | Process Probe Table for Hash Join
| Residual Predicate(s)
| | #Predicates = 2
| Insert Into Sorted Temp Table ID = t3
| | #Columns = 12
| | #Sort Key Columns = 1
| | | Key 1: (Ascending)
| | Sortheap Allocation Parameters:
| | | #Rows = 16.000000
| | | Row Width = 240
| | Piped
| Access Temp Table ID = t3
| | #Columns = 12
| | Relation Scan
| | | Prefetch: Eligible
| Residual Predicate(s)
| | #Predicates = 2
Return Data to Application
| #Columns = 19| | | Prefetch: Eligible
| Residual Predicate(s)
| | #Predicates = 2
Return Data to Application
| #Columns = 19
End of section
Optimizer Plan:
RETURN
( 1)
|
MSJOIN
( 2)
/----------------/ \--------------------\
TBSCAN *
( 3) *
| |
SORT TBSCAN
( 4) ( 13)
| |
HSJOIN SORT
( 5) ( 14)
/-----/ \-----\ |
FETCH FETCH FILTER
( 6) (----) ( 15)
/-/ \ /-/ \ |
IXSCAN Table: RIDSCN Table: HSJOIN
( 6) PROD ( 9) PROD ( 16)
| DEPARTMENT | WAREHOUSE_ORDER /------/ \------\
Index: SORT IXSCAN IXSCAN
PROD ( 10) ( 17) ( 18)
DEPARTMENTINDIVISI | / \ / \
IXSCAN Index: Table: Index: Table:
( 11) PROD PROD PROD PROD
/ \ IX_2_WIZ5158 INVENTORY IX_14_WIZ4554 INVENTORY_ENTRY
Index: Table:
PROD PROD
PK_WAREHOUSE_ORDER WAREHOUSE_ORDER
|
Last edited by mlokesh834; 06-27-11 at 10:20.
Reason: How Analyze the GRAPH and Following Information.
|
06-27-11, 09:22
|
|
Registered User
|
|
Join Date: Jan 2003
Posts: 3,575
|
|
|
|
06-27-11, 16:21
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
|
|
The nested-table-expression(sub query) in the query may be unnecessary.
Because, position of ON clauses define the logical sequence of joines, like the following example:
In the example, I added unnecessary parentheses as comments for ease of understandings.
Example:
Code:
SELECT WHO.BIL_DIV_NO
, WHO.STO_NO
, WHO.DPT_NO
, WHO.WHS_NO
, WHO.WHS_ORD_NO
, WHO.ORD_DT
, WHO.ORD_CST_AM
, WHO.SAT_DT
, DPT.DPT_NAM_TX
, IE .ETY_NO
, IE .ETY_TYP_CD
, IE .STO_CRT_FL
, IE .ICD_FL
, IE .CRE_INE_CD
, IE .RAS_UPD_ID
, IE .INV_NO
, IE .ETY_DSC_TX
, IE .RAS_UPD_TS
, IE .SRC_DOC_NO
FROM
--(
PROD.DEPARTMENT DPT
INNER JOIN
PROD.WAREHOUSE_ORDER WHO
ON DPT.DPT_NO = WHO.DPT_NO
AND DPT.BIL_DIV_NO = WHO.BIL_DIV_NO
--)
LEFT JOIN
--(
PROD.INVENTORY INV
INNER JOIN
PROD.INVENTORY_ENTRY IE
ON IE .INV_NO = INV.INV_NO
AND IE .ETY_TYP_CD = 'AWF'
AND IE .ICD_FL = '1'
--)
ON INV.BIL_DIV_NO = WHO.BIL_DIV_NO
AND INV.STO_NO = WHO.STO_NO
AND INV.DPT_NO = WHO.DPT_NO
AND IE .SRC_DOC_NO = CONCAT(WHO.WHS_NO , WHO.WHS_ORD_NO)
AND IE .ETY_DT = WHO.ORD_DT
WHERE
WHO.BIL_DIV_NO = '011' -- DPT.BIL_DIV_NO = WHO.BIL_DIV_NO
AND WHO.STO_NO = '00245'
AND WHO.SAT_DT = '2011-03-05'
ORDER BY
WHO.ORD_DT
, WHO.DPT_NO
, WHO.WHS_ORD_NO
, WHO.WHS_NO
, WHO.ORD_CST_AM
WITH UR
;
Candidate of indexes.
1) PROD.WAREHOUSE_ORDER WHO
Index on:
Code:
BIL_DIV_NO -- = '011'
STO_NO -- = '00245'
WHO.SAT_DT -- = '2011-03-05'
ORD_DT -- order by 1
DPT_NO -- order by 2
WHS_ORD_NO -- order by 3
WHS_NO -- order by 4
ORD_CST_AM -- order by 5
2) PROD.DEPARTMENT DPT
Index on:
Code:
BIL_DIV_NO -- = '011'
DPT_NO -- = WHO.DPT_NO
DPT_NAM_TX -- (Index only)
3) PROD.INVENTORY INV
Index on:
Code:
BIL_DIV_NO -- = WHO.BIL_DIV_NO
STO_NO -- = WHO.STO_NO
DPT_NO -- = WHO.DPT_NO
INV_NO -- = IE. INV_NO
4) PROD.INVENTORY_ENTRY IE
Index on:
Code:
ETY_TYP_CD -- = 'AWF'
ICD_FL -- = '1'
ETY_DT -- = WHO.ORD_DT
SRC_DOC_NO -- = CONCAT(WHO.WHS_NO , WHO.WHS_ORD_NO)
INV_NO -- = INV.INV_NO
(Add following columns to let DB2 choose Index only access)
RAS_UPD_TS
RAS_UPD_ID
ETY_DSC_TX
CRE_INE_CD
STO_CRT_FL
ETY_NO
|
Last edited by tonkuma; 06-28-11 at 14:43.
Reason: Remove "= CONCAT(...)" in SELECT list. Change INV.INV_NO in SELECT list to IE.INV_NO. Add "logical".
|
06-28-11, 00:54
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
If number of rows of INV(PROD.INVENTORY) joined to a IE(PROD.INVENTORY_ENTRY) was one
and number of rows of DPT(PROD.DEPARTMENT) joined to a WHO(PROD.WAREHOUSE_ORDER) was also one,
(I guessed that the possibility of the conditions was high.)
then the query might be rewritten like the following example.
Even though, performance improvement may not be expected,
number of joins reduced to one
and I felt that the relationships of source of returned information got easy to understand.
Example:
Code:
SELECT WHO.BIL_DIV_NO
, WHO.STO_NO
, WHO.DPT_NO
, WHO.WHS_NO
, WHO.WHS_ORD_NO
, WHO.ORD_DT
, WHO.ORD_CST_AM
, WHO.SAT_DT
, (SELECT DPT.DPT_NAM_TX
FROM PROD.DEPARTMENT DPT
WHERE DPT.DPT_NO = WHO.DPT_NO
AND DPT.BIL_DIV_NO = WHO.BIL_DIV_NO
) AS DPT_NAM_TX
, IE .ETY_NO
, IE .ETY_TYP_CD
, IE .STO_CRT_FL
, IE .ICD_FL
, IE .CRE_INE_CD
, IE .RAS_UPD_ID
, IE .INV_NO
, IE .ETY_DSC_TX
, IE .RAS_UPD_TS
, IE .SRC_DOC_NO
FROM
PROD.WAREHOUSE_ORDER WHO
LEFT JOIN
LATERAL
(SELECT IE.*
FROM PROD.INVENTORY_ENTRY IE
WHERE EXISTS
(SELECT 0
FROM PROD.INVENTORY INV
WHERE INV.INV_NO = IE .INV_NO
AND INV.BIL_DIV_NO = WHO.BIL_DIV_NO
AND INV.STO_NO = WHO.STO_NO
AND INV.DPT_NO = WHO.DPT_NO
)
) IE
ON IE .ETY_TYP_CD = 'AWF'
AND IE .ICD_FL = '1'
AND IE .SRC_DOC_NO = CONCAT(WHO.WHS_NO , WHO.WHS_ORD_NO)
AND IE .ETY_DT = WHO.ORD_DT
WHERE
WHO.BIL_DIV_NO = '011' -- DPT.BIL_DIV_NO = WHO.BIL_DIV_NO
AND WHO.STO_NO = '00245'
AND WHO.SAT_DT = '2011-03-05'
ORDER BY
WHO.ORD_DT
, WHO.DPT_NO
, WHO.WHS_ORD_NO
, WHO.WHS_NO
, WHO.ORD_CST_AM
WITH UR
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 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
HTML code is Off
|
|
|
|
|
dBforums
>
db2explain(db2explan)
