提高查询性能- DB2 SQL

Question

我对SQL很陌生，我正在尝试用DB2编写一个查询。但我认为这是相当低效的，因为它需要更多的资源。如果有人能提供任何能使下面的查询更有效的建议，我会很感激的。我知道需要知道实际的需求/系统知识才能提供更好的建议，但首先看一下，如果你们认为有什么事情搞砸了，请告诉我。提前谢谢。

干杯，DK。

SELECT LIF.EMPID, LIF.BPA_NR , YEAR(LIF.BEG_DTE) ,MONTH(LIF.BEG_DTE) ,LIF.TFIDZ
     , LVA.TRFIDZ, UZA.TRFIDZ, YEAR(LIF.BEG_DTE) - YEAR(KAR.DOB)
     , ((YEAR(APK.TERMDT) - YEAR(APK.BEGDAT)) * 12 + MONTH(APK.TERMDT) - MONTH(APK.BEGDAT))
     , KAR.DTHSUM, LVA.LVAGELD,  UZA.UZAGELD,  APK.EMIRKA
     , LVA.EMILVA, UZA.EMIUZA , APK.EPROM, LVA.EPROM, UZA.EPROM
     , LIF.LOANNR ,LIF.SUMAMT , MONTH(JOU.ZDATE), YEAR(JOU.ZDATE)
     , (YEAR(JOU.ZDATE) - YEAR(APK.BEGDAT))*12 + MONTH(JOU.ZDATE) - MONTH(APK.BEGDAT)
     , COALESCE(JOU.TYP,'OTH') , COALESCE(PRO1.BOOKEMI,0)
     , COALESCE(PRO2.BOOKEMI,0),COALESCE(PRO3.BOOKEMI,0)                  
FROM DEVDBA.LIFE LIF                                                    
LEFT OUTER JOIN (                                                         
    SELECT ( CASE WHEN JU6.REASN = 'AAA' 
                   AND NOT EXISTS (
                      SELECT 1 FROM DEVDBA.LOGTBL JU7             
                      WHERE JU7.EMPID=JU6.EMPID 
                        AND JU7.GANID>JU6.GANID     
                        AND JU7.ZDATE=JU6.ZDATE 
                        AND JU7.REASN='BBB'
                   ) THEN 'KRE'
                  WHEN JU6.REASN IN ('EEE','DDD') 
                     THEN 'SON'                   
                  ELSE 'OTH'                                                     
               END) AS TYP
         , JU6.EMPID, JU6.REASN, JU6.ZDATE, JU6.GANID     
    FROM DEVDBA.LOGTBL JU6                                            
    WHERE JU6.REASN IN ('AAA','EEE','DDD')                            
) JOU                                                               
    ON LIF.EMPID = JOU.EMPID                                  
   AND LIF.ZUNID = JOU.GANID                                  
LEFT OUTER JOIN DEVDBA.LVAGR LVA                                   
    ON JOU.EMPID = LVA.EMPID                                  
   AND JOU.GANID = LVA.ZUNID                                  
LEFT OUTER JOIN DEVDBA.UZAGR UZA                                   
    ON JOU.EMPID = UZA.EMPID                                  
   AND JOU.GANID = UZA.ZUNID                                  
LEFT OUTER JOIN DEVDBA.APKGR APK                                   
    ON  JOU.EMPID = APK.EMPID                                  
   AND JOU.GANID = APK.ZUNID                                  
LEFT OUTER JOIN DEVDBA.KARGR KAR                                  
    ON  JOU.EMPID = KAR.EMPID                                  
   AND JOU.GANID = KAR.ZUNID                                  
LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI      
    FROM DEVDBA.COMMS PR1, DEVDBA.LOGTBL JU1                   
    WHERE PR1.PROVART IN ('789')                             
      AND PR1.EMPID = JU1.EMPID                            
      AND PR1.GANID = JU1.GANID                            
) PRO1                                                   
    ON  JOU.EMPID = PRO1.EMPID                           
   AND JOU.GANID = PRO1.GANID                           
LEFT OUTER JOIN (                                             
    SELECT PR3.EMPID, PR3.GANID, PR3.BOOKEMI
    FROM DEVDBA.COMMS PR3, DEVDBA.LOGTBL JU3             
    WHERE PR3.PROVART IN ('123')                             
      AND PR3.EMPID = JU3.EMPID                            
      AND PR3.GANID = JU3.GANID                            
) PRO2                                                   
    ON  JOU.EMPID = PRO2.EMPID                           
   AND JOU.GANID = PRO2.GANID                           
LEFT OUTER JOIN (                                             
    SELECT PR5.EMPID, PR5.GANID, PR5.BOOKEMI
    FROM DEVDBA.COMMS PR5, DEVDBA.LOGTBL JU5             
    WHERE PR5.PROVART IN ('456')                             
      AND PR5.EMPID = JU5.EMPID                            
      AND PR5.GANID = JU5.GANID                            
) PRO3                                                   
    ON  JOU.EMPID = PRO3.EMPID                           
    AND JOU.GANID = PRO3.GANID                           
WHERE LIF.BPA_NR IN ('001','002') 
  AND JOU.TYP <> 'OTH'  
  AND LIF.BEG_DTE BETWEEN '01.01.2022' AND '31.12.2099'
ORDER BY LIF.BPA_NR,LIF.EMPID WITH UR;

Answer 1

不是回答本身，但它是渴望得到一个评论，所以我会在这里添加它无论如何。

在不知道更多细节的情况下说得太多很困难。您应该从查看访问计划开始。我发现db2caem非常好，因为它为每个运算符输出估计基数和实际基数。如果一个操作符有很大的不同，那么通常是一个提示，说明从哪里开始调查。

db2advis菅直人给出了一些关于缺少索引的提示，但不要认为它是理所当然的，它经常建议冗余的索引，所以您必须调查它的结果。如果您没有访问db2advis的权限，您可能需要尝试：

索引顾问

它是一个python脚本，它生成与db2advis相同的索引候选集，然后评估索引组合的powerset及其估计的改进，并选择最佳的1-索引、2-索引、.、n-索引组合。powerset的基数增长很快，因此可以为要研究的索引组合设置一个下限和上限。自担风险使用

关于代码本身的一些观察。在过去，我在优化器中看到了一些神秘的效果，当混合“逗号”-joins和ansi-联接时。我建议你更换如下结构：

LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI      
    FROM DEVDBA.COMMS PR1, DEVDBA.LOGTBL JU1                   
    WHERE PR1.PROVART IN ('789')                             
      AND PR1.EMPID = JU1.EMPID                            
      AND PR1.GANID = JU1.GANID                            
) PRO1

通过以下方式：

LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI      
    FROM DEVDBA.COMMS PR1
    JOIN DEVDBA.LOGTBL JU1
        ON PR1.EMPID = JU1.EMPID                            
       AND PR1.GANID = JU1.GANID                    
    WHERE PR1.PROVART IN ('789')                             
) PRO1

在PRO1、PRO2和PRO3中，您似乎重复了相同的连接：

LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI      
    FROM DEVDBA.COMMS PR1, DEVDBA.LOGTBL JU1                   
    WHERE PR1.PROVART IN ('789')                             
      AND PR1.EMPID = JU1.EMPID                            
      AND PR1.GANID = JU1.GANID                            
) PRO1                                                   
    ON  JOU.EMPID = PRO1.EMPID                           
   AND JOU.GANID = PRO1.GANID                           
LEFT OUTER JOIN (                                             
    SELECT PR3.EMPID, PR3.GANID, PR3.BOOKEMI
    FROM DEVDBA.COMMS PR3, DEVDBA.LOGTBL JU3             
    WHERE PR3.PROVART IN ('123')                             
      AND PR3.EMPID = JU3.EMPID                            
      AND PR3.GANID = JU3.GANID                            
) PRO2                                                   
    ON  JOU.EMPID = PRO2.EMPID                           
    AND JOU.GANID = PRO2.GANID 
...

您可能想调查一下，如果您可以将词组改为：

LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI, PR1.PROVART      
    FROM DEVDBA.COMMS PR1
    JOIN DEVDBA.LOGTBL JU1
        ON PR1.EMPID = JU1.EMPID                            
       AND PR1.GANID = JU1.GANID                    
    WHERE PR1.PROVART IN ('789', '123', '456')                             
) PRO
    ON  JOU.EMPID = PRO.EMPID                           
   AND JOU.GANID = PRO.GANID 

，然后在选择中使用PROVART上的case表达式来跟踪结果的来源。

Occationally还可以帮助使用横向将连接谓词手动推送到派生表中。

LEFT OUTER JOIN LATERAL (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI, PR1.PROVART      
    FROM DEVDBA.COMMS PR1
    JOIN DEVDBA.LOGTBL JU1
        ON PR1.EMPID = JU1.EMPID                            
       AND PR1.GANID = JU1.GANID                    
    WHERE PR1.PROVART IN ('789', '123', '456')                             
      AND JOU.EMPID = PR1.EMPID                           
      AND JOU.GANID = PR1.GANID 
) PRO
    ON  JOU.EMPID = PRO.EMPID                           
   AND JOU.GANID = PRO.GANID 

只是一些想法。