sql - DB2 Union 导致超时？

Question

第一次使用 DB2。我有一个执行联合的存储过程，无论如何它都会尝试这样做。该过程超时。联合两侧的 select 语句单独执行时执行速度很快，不会出现任何问题。工会为什么要这么做？

这里是 proc 的一些 sudo 代码，假设数据类型是 int 或 string，并且它们在比较或联合时匹配，除非 SQL 中另有说明:

DECLARE Foo CURSOR WITH RETURN FOR
  Select STRIP(A.Name) as my_Name, 
     Case A.Number
        when 2 then '(' || strip(char(A.Number)) || ')' strip(B.num)
        when 3 then '(' || strip(char(A.Number)) || ')' strip(C.num)
        when 4 then '(' || strip(char(A.Number)) || ')' strip(D.num)
        when 5 then '(' || strip(char(A.Number)) || ')' strip(E.num)
        when 6 then '(' || strip(char(A.Number)) || ')' strip(F.num)
        end as my_number
  FROM A
       left outer join B on A.Number= 2 and A.Name = B.Name
       left outer join C on A.Number= 3 and C.Name = B.Name
       left outer join D on A.Number= 4 and D.Name = B.Name
       left outer join E on A.Number= 5 and E.Name = B.Name
       left outer join F on A.Number= 6 and F.Name = B.Name 
      ,session.Temp_Result X
  WHERE X.ID = A.ID
  GROUP BY A.Number, A.Name, B.Name, C.Name, D.Name, E.Name, F.Name
for fetch only ur;

DROP TABLE Session.Temp_Result;

DECLARE GLOBAL TEMPORARY TABLE session.Temp_Result
(     ID DECIMAL(18,0)
);

INSERT INTO session.Temp_Result
        select X.ID
        from Z, Y, X, Q
        where Z.num = 6
          and Z.ID = Y.ID2
          and Y.GROUPA = 'ABC'
          and Y.GROUPB = 'DEF'
          and Y.ID = X.ID2
          and X.ID = Q.ID            
        union
       select W.ID
        from Z, Y, W
        where Z.num = 6
          and Z.ID = Y.ID2
          and Y.GROUPA = 'ABC'
          and Y.GROUPB = 'DEF'
          and Y.ID = W.ID2
        group by ID;


  OPEN C_HIERARCHIES;

Answer 1

尝试使用“with”语句进行查询。

DECLARE GLOBAL TEMPORARY TABLE session.Temp_Result (
ID DECIMAL(18,0))
WITH REPLACE;

INSERT INTO session.Temp_Result
(ID)
WITH Q1(Y_ID)
AS (
SELECT Y.ID
FROM Z
    INNER JOIN Y ON Z.ID = Y.ID2
WHERE Z.NUM = 6 AND Y.GROUPA = 'ABC' AND Y.GROUPB = 'DEF')

SELECT X.ID
FROM X
    INNER JOIN Q1 ON X.ID2 = Q1.Y_ID
WHERE EXISTS(SELECT 1 FROM Q WHERE Q.ID = X.ID)
UNION
SELECT DISTINCT W.ID
FROM W
    INNER JOIN Q1 ON W.ID2 = Q1.Y_ID

如果没有帮助，请尝试对 select 语句使用“解释 SQL”。也许你应该创建一些索引...