Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Unanswered: Table Scan on DELETE

    ..any idea (besides having appropriate indexes) why the following will scan on the Delete...all other operations are Index Seek

    Code:
    DELETE FROM HR_TLO_ORG X                                 
     WHERE EXISTS (                                          
           SELECT * FROM (                                   
                       SELECT DISTINCT B.IONS_ID, A.ORG_CD   
                         FROM HR_PLAN_ORGS          A        
                   INNER JOIN HR_PLAN_USER_SCOPE    B        
                           ON A.R_CD             =  B.R_CODE 
                   INNER JOIN HR_TLO_ORG            C        
                           ON B.IONS_ID          =  C.IONS_ID
                        WHERE A.ACCESS_IND       = 'N'       
                          AND A.ORG_CD           =  C.LVL2_CD
                        UNION                                
                       SELECT DISTINCT B.IONS_ID, A.ORG_CD   
                         FROM HR_PLAN_ORGS_I        A        
                   INNER JOIN HR_PLAN_USER_SCOPE_I  B        
                           ON A.R_CD             =  B.R_CODE 
                   INNER JOIN HR_TLO_ORG            C        
                           ON B.IONS_ID          =  C.IONS_ID
                        WHERE A.ACCESS_IND       = 'N'        
                          AND A.ORG_CD           =  C.LVL2_CD 
                    ) AS XXX                             
       WHERE X.IONS_ID = XXX.IONS_ID                     
         AND X.LVL2_CD = XXX.ORG_CD                      
           );                                            
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Explain:

    Code:
    
    tname                tabno  accesstype  matchcols  accesscreator  accessname 
    ------------------  ------  ----------  ---------  -------------  -----------
    hr_tlo_org               1  r                   0                            
    hr_plan_user_scope       6  i                   1  bxhrspda       sbxpi064   
    hr_plan_orgs_i           5  i                   2  bxhrspda       sbxpi063   
    hr_tlo_org               7  i                   2  bxhrspda       sbxa0066   
                             0                      0                            
    hr_plan_user_scope       3  i                   1  bxhrspda       sbxp0064   
    hr_plan_orgs             2  i                   3  bxhrspda       sbx10063   
    hr_tlo_org               4  i                   2  bxhrspda       sbxa0066   
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    See I figure that the DELETE should be using the index sbxa0066, just like it does for the SELECTS in the derived table...it's even more simple than those...

    So why is it scanning?

    Because of the derived table???
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Should this internalize as a Join?

    And to that end..does db2 9.1 have a

    DELETE a FROM tableA a Join TableB b


    Syntax yet?

    Ohhh...this z/OS
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Brett, I will take a stab at this.

    The table space scan on HR_TLO_ORG if because there is no Where clause that filters out any rows (at the time the SQL is optimized).

    Every row in HR_TLO_ORG has to be read and checked to see if it EXISTS in the subquery.

    Additionally, the subquery has unknown values because of the nested (derived) table so the 'join' predicates (X.cols = XXX.cols).

    Since it has to read every row anyway, it is better to reduce I/O be eliminating the extra Index page accesses.

    As a check of this try changing the DELETE FROM ... to SELECT col-list FROM... and see if the Explain still does a table space scan.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why didn't you refer X directly in SELECTs in the nested table expression?
    I guessed that you couldn't see X in the nested table expression.

    If so,
    please try to use TABLE keyword, like this:
    (You can see examples of TABLE keyword in Table 71 in V9 SQL Reference or Table 50 in V8 SQL Reference of DB2 for z/OS.)

    Example 1:
    Code:
    DELETE FROM HR_TLO_ORG X
     WHERE EXISTS (
           SELECT * 
             FROM TABLE
                  (
                   SELECT 0 AS dummy
                     FROM HR_PLAN_ORGS          A
                    INNER JOIN
                          HR_PLAN_USER_SCOPE    B
                      ON  A.R_CD             =  B.R_CODE
                    WHERE A.ACCESS_IND       = 'N'
                      AND A.ORG_CD           =  X.LVL2_CD
                      AND B.IONS_ID          =  X.IONS_ID
                   UNION ALL
                   SELECT 0 AS dummy
                     FROM HR_PLAN_ORGS_I        A
                    INNER JOIN
                          HR_PLAN_USER_SCOPE_I  B
                      ON  A.R_CD             =  B.R_CODE
                    WHERE A.ACCESS_IND       = 'N'
                      AND A.ORG_CD           =  X.LVL2_CD
                      AND B.IONS_ID          =  X.IONS_ID
                  ) AS XXX
           )
    ;

    More,
    I think you can remove the nested table expression, like these.

    Example 2-A:
    Code:
    DELETE FROM HR_TLO_ORG X
     WHERE EXISTS (
           SELECT 0 AS dummy
             FROM HR_PLAN_ORGS          A
            INNER JOIN
                  HR_PLAN_USER_SCOPE    B
              ON  A.R_CD             =  B.R_CODE
            WHERE A.ACCESS_IND       = 'N'
              AND A.ORG_CD           =  X.LVL2_CD
              AND B.IONS_ID          =  X.IONS_ID
           UNION ALL
           SELECT 0 AS dummy
             FROM HR_PLAN_ORGS_I        A
            INNER JOIN
                  HR_PLAN_USER_SCOPE_I  B
              ON  A.R_CD             =  B.R_CODE
            WHERE A.ACCESS_IND       = 'N'
              AND A.ORG_CD           =  X.LVL2_CD
              AND B.IONS_ID          =  X.IONS_ID
           )
    ;

    Example 2-B:
    Code:
    DELETE FROM HR_TLO_ORG X
     WHERE EXISTS (
           SELECT *
             FROM HR_PLAN_ORGS          A
            INNER JOIN
                  HR_PLAN_USER_SCOPE    B
              ON  A.R_CD             =  B.R_CODE
            WHERE A.ACCESS_IND       = 'N'
              AND A.ORG_CD           =  X.LVL2_CD
              AND B.IONS_ID          =  X.IONS_ID
           )
       OR
           EXISTS (
           SELECT *
             FROM HR_PLAN_ORGS_I        A
            INNER JOIN
                  HR_PLAN_USER_SCOPE_I  B
              ON  A.R_CD             =  B.R_CODE
            WHERE A.ACCESS_IND       = 'N'
              AND A.ORG_CD           =  X.LVL2_CD
              AND B.IONS_ID          =  X.IONS_ID
           )
    ;

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Thanks a bunch...I'll give it a try...wonder why I didn't get any notification
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    2-A is still a scan

    Code:
    TNAME                TABNO  ACCESSTYPE  MATCHCOLS  ACCESSNAME          INDEXO
    ------------------  ------  ----------  ---------  ------------------  ------
    XXX                      7  R                   0                      N     
                             0                      0                            
    HR_PLAN_USER_SCOPE       2  I                   0  SBX10064            Y     
    HR_TLO_ORG               3  I                   1  SBXA0066            Y     
    HR_PLAN_ORGS             1  I                   3  SBX10063            Y     
                             0                      0                      N     
    HR_PLAN_USER_SCOPE       5  I                   0  SBX1I064            Y     
    HR_PLAN_ORGS_I           4  I                   1  SBX1I063            N     
    HR_TLO_ORG               6  I                   2  SBXA0066            Y     
                             0                      0                      N
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    2-A is still a scan
    I don't know how to read explain on DB2 for z/OS.

    But,
    From where did XXX come?

  10. #10
    Join Date
    Dec 2005
    Posts
    273
    XXX is the materialised ... FROM TABLE ( select-statement ) AS XXX ... , I suppose.



    depending on the size of the tables, a query like that might work:

    Code:
    DELETE FROM HR_TLO_ORG X                                 
     WHERE ( IONS_ID , LVL2_CD ) IN (                                          
                                            
                       SELECT  B.IONS_ID, A.ORG_CD   
                         FROM HR_PLAN_ORGS          A        
                   INNER JOIN HR_PLAN_USER_SCOPE    B        
                           ON A.R_CD             =  B.R_CODE 
                   INNER JOIN HR_TLO_ORG            C        
                           ON B.IONS_ID          =  C.IONS_ID
                        WHERE A.ACCESS_IND       = 'N'       
                          AND A.ORG_CD           =  C.LVL2_CD
                        UNION                                
                       SELECT  B.IONS_ID, A.ORG_CD   
                         FROM HR_PLAN_ORGS_I        A        
                   INNER JOIN HR_PLAN_USER_SCOPE_I  B        
                           ON A.R_CD             =  B.R_CODE 
                   INNER JOIN HR_TLO_ORG            C        
                           ON B.IONS_ID          =  C.IONS_ID
                        WHERE A.ACCESS_IND       = 'N'        
                          AND A.ORG_CD           =  C.LVL2_CD 
                      
           );
    Last edited by umayer; 09-14-10 at 18:10.

Posting Permissions

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