Oracle 查询 - 优化

Question

我写了下面的查询，其中有很多'AND'运算符，我想知道如何优化下面查询的性能[我可以删除一些'AND'运算符]

    SELECT I.date,
              K.somcolumn,
              L.somcolumn,
              D.somcolumn
         FROM Table1 I,
              Table2 K,
              Table3 L,
              Table4 D
        WHERE I._ID = K._ID
              AND K.ID = L._ID
              AND L._ID = I._ID
              AND I._CODE = L._CODE
              AND K.ID = D._ID(+)
              AND L._ID IN ( SELECT _id
                                       FROM I
                                      WHERE UPPER (someflag) = 'TRUE'
                                   GROUP BY _id
                                     HAVING COUNT (*) > 1)
              AND L._ID IN ( SELECT _id
                                       FROM I
                                      WHERE UPPER (code) = 'OPEN'
                                   GROUP BY _id
                                     HAVING COUNT (*) > 1)
     ORDER BY I._ID, I._CODE;

Answer 1

据我所知，您不能组合任何条件，但您可以通过使用标准 JOIN 改进查询并减少 AND 运算符的数量语法:

SELECT I.date,
  K.somcolumn,
  L.somcolumn,
  D.somcolumn
FROM Table1 I
INNER JOIN Table2 K ON I._ID = K._ID
INNER JOIN Table3 L ON K.ID = L._ID
LEFT JOIN Table4 D ON K.ID = D._ID
WHERE L._ID IN ( SELECT _id
   FROM I
   WHERE UPPER (someflag) = 'TRUE'
   GROUP BY _id
   HAVING COUNT (*) > 1)
 AND L._ID IN ( SELECT _id
   FROM I
   WHERE UPPER (code) = 'OPEN'
   GROUP BY _id
   HAVING COUNT (*) > 1)
ORDER BY I._ID, I._CODE;

以此为基础，如果您加入子查询条件而不是使用相关子查询，您可能获得优化提升。没有保证，但像这样的事情可能会有所帮助:

SELECT I.date,
  K.somcolumn,
  L.somcolumn,
  D.somcolumn
FROM Table1 I
INNER JOIN Table2 K ON I._ID = K._ID
INNER JOIN Table3 L ON K.ID = L._ID
LEFT JOIN Table4 D ON K.ID = D._ID
INNER JOIN (
    SELECT _id
    FROM I
    WHERE UPPER (someflag) = 'TRUE'
    GROUP BY _id
    HAVING COUNT (*) > 1
  ) someflagtrue ON L._ID = someflagtrue._id
INNER JOIN (
    SELECT _id
     FROM I
     WHERE UPPER (code) = 'OPEN'
     GROUP BY _id
     HAVING COUNT (*) > 1
  ) codeopen ON L._ID = codeopen._id
ORDER BY I._ID, I._CODE;