Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    6

    Unhappy Unanswered: 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 11:20. Reason: How Analyze the GRAPH and Following Information.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 15:43. Reason: Remove "= CONCAT(...)" in SELECT list. Change INV.INV_NO in SELECT list to IE.INV_NO. Add "logical".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •