通过删除'exists‘和'not exists’优化Oracle查询

Question

我最近在oracle数据库上移植了一段代码到生产环境中，其中一位更有经验的开发人员审阅了它，他提到我有太多的exists和not exists语句，应该有一种方法来删除它们，但他已经很长时间没有使用它了，也不太记得它是如何工作的。目前，我正在返回并使这段代码更易维护，因为它可能在未来几年随着业务逻辑/需求的变化而多次更改，我想继续优化它，同时使它更可维护。

我尝试过查找它，但我所能找到的只是关于用not exists替换not in以及不返回实际结果的建议。

因此，我想知道可以做些什么来优化exists/not exists，或者是否有一种方法可以编写exists/not exists，以便oracle在内部对其进行优化(可能比我做得更好)。

例如，如何优化以下内容？

UPDATE
    SCOTT.TABLE_N N
SET
    N.VALUE_1 = 'Data!'
WHERE
    N.VALUE_2 = 'Y'
    AND
    EXISTS
    (
        SELECT
            1
        FROM
            SCOTT.TABLE_Q Q
        WHERE
            N.ID = Q.N_ID
    )
    AND
    NOT EXISTS
    (
        SELECT
            1
        FROM
            SCOTT.TABLE_W W
        WHERE
            N.ID = W.N_ID
    )

Answer 1

你的陈述在我看来完全没问题。

在任何优化任务中，都不要考虑模式。不要认为，"(not) exists是坏的和缓慢的，(not) in是超级酷和快速“。

想一想，数据库在每一步做了多少工作，你如何衡量它？

一个简单的例子：

--非IN:

23:59:41 HR@sandbox> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.03
23:59:43 HR@sandbox> set autotrace traceonly explain statistics
23:59:49 HR@sandbox> select country_id from countries where country_id not in (select country_id from locations);

11 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1748518851

------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |     1 |     6 |     4   (0)| 00:00:01 |
|*  1 |  FILTER                |                 |       |       |            |          |
|   2 |   NESTED LOOPS ANTI SNA|                 |    11 |    66 |     4  (75)| 00:00:01 |
|   3 |    INDEX FULL SCAN     | COUNTRY_C_ID_PK |    25 |    75 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN    | LOC_COUNTRY_IX  |    13 |    39 |     0   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL    | LOCATIONS       |     1 |     3 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( NOT EXISTS (SELECT 0 FROM "LOCATIONS" "LOCATIONS" WHERE
              "COUNTRY_ID" IS NULL))
   4 - access("COUNTRY_ID"="COUNTRY_ID")
   5 - filter("COUNTRY_ID" IS NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          8  physical reads
          0  redo size
        446  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

--不存在

23:59:57 HR@sandbox> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.17
00:00:02 HR@sandbox> select country_id from countries c where not exists (select 1 from locations l where l.country_id = c.country_id );

11 rows selected.

Elapsed: 00:00:00.30

Execution Plan
----------------------------------------------------------
Plan hash value: 840074837

-------------------------------------------------------------------------------------
| Id  | Operation         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                 |    11 |    66 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI|                 |    11 |    66 |     1   (0)| 00:00:01 |
|   2 |   INDEX FULL SCAN | COUNTRY_C_ID_PK |    25 |    75 |     1   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| LOC_COUNTRY_IX  |    13 |    39 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          2  physical reads
          0  redo size
        446  bytes sent via SQL*Net to client
        363  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

在这个例子中，NOT IN读取了两倍的数据库块，并执行了更复杂的过滤--问问你自己，为什么你会选择它而不是NOT EXISTS？

Answer 2

当您需要时，没有理由避免使用EXISTS或NOT EXISTS。在您给出的示例中，这可能正是您想要使用的。

典型的困境是使用IN/NOT IN，还是EXISTS/NOT EXISTS。它们的评估方式完全不同，根据您的具体情况，它们可能更快，也可能更慢。

有关更多详细信息，请参阅here。

Answer 3

我不知道它是否更快，但这里有一种不使用EXISTS/NOT EXISTS编写它的方法

MERGE INTO TABLE_N T
USING (
  SELECT N.ID, 'Data!' AS NEW_VALUE_1
  FROM SCOTT.TABLE_N N
  INNER JOIN SCOTT.TABLE_Q Q
      ON Q.N_ID = N.ID
  LEFT JOIN SCOTT.TABLE_W W
      ON W.N_ID = N.ID
  WHERE N.VALUE_2 = 'Y'
  AND W.ID IS NULL
) X
ON ( T.ID = X.ID )
WHEN MATCHED THEN UPDATE
    SET T.VALUE_1 = X.NEW_VALUE_1;