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;

- Code's

---

如果返回的行数很少，您可以尝试使用标量子查询将计数作为新的返回列从表I中获取。您还可以尝试使用WITH子句从表I构建临时表。这只是一些想法。我无法给出答案，因为我无法验证我的想法。 - user2672165

也许使用EXISTS比使用IN快得多，因为可以使用索引。EXISTS ( SELECT NULL FROM I WHERE L._id=_id AND UPPER(code) = 'OPEN' GROUP BY _id HAVING COUNT (*) > 1)。由于_id、code和someflag可能是位图索引的候选项，因此可以使用索引。 - user2672165

Answer 1

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

```html

据我所知，您无法组合任何条件，但是您可以改进查询并通过使用标准的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;

- Ed Gibbs

Answer 2

你可以用一个子查询替换这两个子查询。

旧的子查询：

  SELECT _id
    FROM I
   WHERE UPPER (someflag) = 'TRUE'
GROUP BY _id
  HAVING COUNT (*) > 1)

  SELECT _id
    FROM I
   WHERE UPPER (code) = 'OPEN'
GROUP BY _id
  HAVING COUNT (*) > 1)

新子查询：

SELECT _ID
  FROM I
 GROUP BY _ID
HAVING COUNT(CASE WHEN UPPER(SOMEFLAG) = 'TRUE' THEN 1 ELSE 0 END) > 0
   AND COUNT(CASE WHEN UPPER(CODE) = 'OPEN' THEN 1 ELSE 0 END) > 0

在大多数情况下，这应该会更快一些，因为它可以减少完整表扫描和连接的数量。但是很难确定它是否在您的系统上更快，因为优化器有太多可能的选择。

清理查询后，性能调优的下一步是生成解释计划。运行explain plan for select ...;，然后运行select * from table(dbms_xplan.display);。这将显示查询的执行方式，这可能会给您提示哪些地方较慢，以及哪些可以改进。如果需要更多帮助，请将完整的解释计划输出添加到您的问题中。还可以添加有关相关表中行数、返回行数以及索引的信息。

- Jon Heller