sql - COUNT() 上的 Oracle 性能问题

Question

我使用的是 Oracle 11g，主表有大约 10m 条记录。这是我的查询:

SELECT COUNT (*)
  FROM CONTACT c INNER JOIN STATUS S ON C.STATUS = S.STATUS
 WHERE C.USER = 1 AND S.REQUIRE = 1 AND ROWNUM = 1;

成本是 3736，但是当我将其更改为这种形式时:

SELECT COUNT (*) FROM
  (SELECT 1 FROM CONTACT c INNER JOIN STATUS S ON C.STATUS = S.STATUS
  WHERE C.USER = 1 AND S.REQUIRE = 1 AND ROWNUM = 1);

成本变成了5!这两个查询有什么区别？

以下是两个查询的解释计划:

第一个查询:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                         |     1 |    10 |  3736   (1)| 00:00:45 |
|   1 |  SORT AGGREGATE                |                         |     1 |    10 |            |          |
|*  2 |   COUNT STOPKEY                |                         |       |       |            |          |
|   3 |    NESTED LOOPS                |                         |  4627 | 46270 |  3736   (1)| 00:00:45 |
|   4 |     TABLE ACCESS BY INDEX ROWID| CONTACT                 |  6610 | 33050 |  3736   (1)| 00:00:45 |
|*  5 |      INDEX RANGE SCAN          | IX_CONTACT_USR          |  6610 |       |    20   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | IX_CONTACT_STATUS       |     1 |     5 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM=1)
   5 - access("C"."USER"=1)
   6 - access("C"."STATUS"="S"."STATUS" AND "S"."REQUIRE"=1)

第二个查询:

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |     1 |       |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                 |                         |     1 |       |            |          |
|   2 |   VIEW                          |                         |     1 |       |     5   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                |                         |       |       |            |          |
|   4 |     NESTED LOOPS                |                         |     2 |    20 |     5   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| CONTACT                 |     3 |    15 |     5   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | IX_CONTACT_USR          |  6610 |       |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | IX_CONTACT_STATUS       |     1 |     5 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - filter(ROWNUM=1)
   6 - access("C"."USER"=1)
   7 - access("C"."STATUS"="S"."STATUS" AND "S"."REQUIRE"=1)

我执行了 2 个查询，第一个有时会花费 45s+(例如第一次运行或更改用户 ID)，否则花费 <1s。我完全不知道为什么它如此不同，也许是数据库缓存？

当我执行第二个查询时，我总是可以在 1 秒内得到结果。所以我认为第二个更好，但我不知道它改进很多的原因。

Answer 1

很可能这只是估计差异，它们将具有相同的执行统计信息。跟踪两者 + tkprof 以获取真实数据。
此外，如果您想了解优化器逻辑背后的更多细节 - 使用事件 10053 进行硬解析。