解決多連接 DB2 的性能問題

執行查詢時收到以下警告

     Performance of this complex query might be sub-optimal Reason Code 2
     DB2 SQL error: SQLCODE: 437, SQLSTATE: 01602, SQLERRMC: 2

查詢如下：

查詢 1

      SELECT 
      DISTINCT
      a.Part_NO,
      a.PART_DESC,
      a.C_Purchase,
      a.Supplier,
      d.Supplier_Order_date as Date1,
      l.Supplier_Order_date as Date2,
      e.Supplier_Order_date as Date3,
      f.Supplier_Order_date as Date4,
      h.Supplier_Order_date as Date5,
      i.Supplier_Order_date as Date6,
      j.Supplier_Order_date as Date7,
      j.D_SUPLR_CPLN_PROM as Date8,
      k.Supplier_Order_date as Date9,
      k.D_SUPLR_CPLN_PROM as Date10,

      FROM
          table100 a 
      JOIN
         table101 b 
         ON a.Part_NO = b.Part_NO 
         AND a.C_Purchase = b.C_ENTRY 
     JOIN
         table102 d        
         ON a.Part_NO = d.Part_NO 
         AND a.Supplier = d.Supplier 
         AND a.C_Purchase = d.C_Purchase
         AND d.C_QLTY = 'ABC' 
     JOIN
         table102 l      
         ON a.Part_NO = l.Part_NO 
         AND a.Supplier = l.Supplier 
         AND a.C_Purchase = l.C_Purchase 
         AND l.C_QLTY = 'AB S' 
      JOIN 
          table102 e      
          ON a.Part_NO = e.Part_NO 
          AND a.Supplier = e.Supplier 
          AND a.C_Purchase = e.C_Purchase
          AND e.C_QLTY = 'CDE' 
      JOIN
          table102 f 
          ON a.Part_NO = f.Part_NO 
          AND a.Supplier = f.Supplier 
          AND a.C_Purchase = f.C_Purchase
          AND f.C_QLTY = 'AEC' 
      JOIN
           table102 h 
           ON a.Part_NO = h.Part_NO 
           AND a.Supplier = h.Supplier 
           AND a.C_Purchase = h.C_Purchase
           AND h.C_QLTY = 'EFG' 
      JOIN
         table102 i 
         ON a.Part_NO = i.Part_NO 
         AND a.Supplier = i.Supplier 
         AND a.C_Purchase = i.C_Purchase
         AND i.C_QLTY = 'AGAG' 
      JOIN
         table102 j 
         ON a.Part_NO = j.Part_NO 
         AND a.Supplier = j.Supplier 
         AND a.C_Purchase = j.C_Purchase
         AND j.C_QLTY = 'CAP' 
      JOIN
          table102 k 
          ON a.Part_NO = k.Part_NO 
          AND a.Supplier = k.Supplier 
          AND a.C_Purchase = k.C_Purchase
          AND k.C_QLTY = 'OTP' 

在上面的查詢中，table102多次使用別名為 b,d,l,e,f,h,i,j,k。此查詢似乎會導致性能問題。

這些是日誌詳細資訊，來自生產伺服器的錯誤消息。當相同的查詢在 Java 程式碼中執行時。

消息：語句太長或太複雜..

SQLCODE=-101, SQLSTATE=54001, DRIVER=3.57.1​​10

Vendor: -101

SQLState: 56098

但是 Fetch 時間是 50 秒。在進行網路搜尋時，上述錯誤的原因如下。

    The join enumeration method was altered due to query complexity

假設 (Part_NO,Supplier,C_Purchase,C_QLTY) 是唯一的，一種常見的技術是像這樣預先聚合數據：

WITH cte AS
( SELECT Part_NO, Supplier, C_Purchase,
       MAX(CASE WHEN C_QLTY = 'ABC' THEN Supplier_Order_date END) AS Date1,
       MAX(CASE WHEN C_QLTY = 'AB S' THEN Supplier_Order_date END) AS Date2,
       ...
    FROM table102 d
    -- not needed to get the result, but might reduce resource usage
    WHERE C_QLTY IN ('ABC', 'AB S', ...)
    GROUP BY Part_NO, Supplier, C_Purchase
  ) 
SELECT 
  DISTINCT
  a.Part_NO,
  a.PART_DESC,
  a.C_Purchase,
  a.Supplier,
  cte.Date1,
  cte.Date2,
  ...

  FROM
      table100 a 
  JOIN
     table101 b 
     ON a.Part_NO = b.Part_NO 
     AND a.C_Purchase = b.C_ENTRY 
 JOIN cte
     ON a.Part_NO = cte.Part_NO 
     AND a.Supplier = cte.Supplier 
     AND a.C_Purchase = cte.C_Purchase

注意，如果任何C_QLTY值不存在，則結果與左連接所有這些 table102 的結果相同。然後你可能需要添加一個

-- outer Select 
WHERE 'ABC' IS NOT NULL 
 AND 'AB S' IS NOT NULL
 ...

或者您添加（感謝@AndriyM）：

-- cte:
HAVING COUNT(*) = 8 -- number of rows/conditions

如果 (Part_NO,Supplier,C_Purchase,C_QLTY) 不是唯一的（但結果不同）

HAVING COUNT(DISTINCT C_QLTY) = 8

我已經解決了上述問題，解決方案就是設置優化級別。

   Set CURRENT QUERY OPTIMIZATION=1 

這解決了這個問題。並且查詢沒有給出錯誤。多個連接沒有問題。關聯

大多數 SQL 語句都使用預設查詢優化類進行了充分優化（大多數是預設的）。任何優化類級別的原始碼預編譯時間和資源消耗主要取決於預編譯查詢的複雜性，尤其是在查詢包含連接和子查詢的情況下

使用以下“經驗法則”來選擇最佳查詢優化類：

   1. Start by using the default query optimization class (5).

   2. If you feel you need an optimization class other than the default,  
      try class 3 or 7.

   3. Use a lower optimization class (0 or 1) for queries that have
      a short runtime (less than 1 second). 

   4.Use a higher optimization class (7 or 9) for queries that have a 
     long runtime (greater than 10 seconds).

   5.Use optimization class 1 if your queries access many tables with 
     join predicates on the same column. *****

要獲取目前使用的優化類級別，請執行以下命令

 VALUES CURRENT QUERY OPTIMIZATION 

分配給與數據庫的新連接的預設值由數據庫配置參數 (DFT_QUERYOPT) 確定。

注意：優化類特定於連接會話。如果優化類從預設值更改，它將特定於它被更改的會話。新的連接/會話將具有預設的優化類

引用自：https://dba.stackexchange.com/questions/153341