Table Scan on DELETE

Brett Kaiser · #1 (**permalink**) 09-03-10, 12:23

..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 Kaiser · #2 (**permalink**) 09-03-10, 12:25

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 Kaiser · #3 (**permalink**) 09-03-10, 12:27

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 Kaiser · #4 (**permalink**) 09-03-10, 13:17

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

Stealth_DBA · #5 (**permalink**) 09-03-10, 14:16

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.

tonkuma · #6 (**permalink**) 09-03-10, 15:33

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
       )
;

Brett Kaiser · #7 (**permalink**) 09-07-10, 13:50

Thanks a bunch...I'll give it a try...wonder why I didn't get any notification

Brett Kaiser · #8 (**permalink**) 09-07-10, 13:59

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

tonkuma · #9 (**permalink**) 09-07-10, 16:47

Quote:

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?

umayer · #10 (**permalink**) 09-14-10, 16:51

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 
                  
       );

Internet Services

Web Development

Digital Marketing

Consumer Tech