Oracle: ROW_NUMBER比ROWNUM快20倍

Question

表格详情：

• table1有2000万条目
• table2有120个条目

这些请求的目标是返回是否存在现有条目，条目数量并不重要。

有人能解释一下为什么吗？

SELECT COUNT(rn) count
FROM
(
 SELECT ROW_NUMBER() OVER (order by t2.id_field) AS rn
 FROM table1 t1
 INNER JOIN table2 t2 ON t2.id_table2 = t1.id_table2
 WHERE t2.id_field = 2
)
WHERE rn < 2;

比起以下内容，它快了20倍：

SELECT COUNT(rn) count
FROM
(
 SELECT 1 rn
 FROM table1 t1
 INNER JOIN table2 t2 ON t2.id_table2 = t1.id_table2
 WHERE t2.id_field = 2
)
WHERE ROWNUM < 2;

- sdespont

---

您知道这两者并不等价吗？第二个查询使用“随机”排序来分配行号，而第一个查询使用了一个定义好的顺序。执行计划给您带来了什么信息？ - user330315

我怀疑优化器只是更擅长优化确定性查询，但没有实际查询计划的任何内容都只是猜测。 - Joachim Isaksson

@a_horse_with_no_name 我忘记注意到这些请求的目的是返回是否存在条目。 条目数量并不重要。 - sdespont

@sdespont 那么你最好使用 EXISTS 了解一下...? - Joachim Isaksson

@ThinkJet，我发誓我尝试了，但是当我尝试显示执行计划时出现了ORA-00905错误... - sdespont

Answer 1

如前所述，ROW_NUMBER()和ROWNUM完全不相同。

第一个是分析函数，使用窗口。顺序由order by命令指定。

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF51198

select
  ename,
  ROW_NUMBER() OVER (order by ename ) AS rn 
from emp
where deptno =20;

ENAME              RN
---------- ----------
ADAMS               1
FORD                2
JONES               3
SCOTT               4
SMITH               5


Execution Plan
----------------------------------------------------------
Plan hash value: 3145491563

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    45 |     4  (25)| 00:00:01 |
|   1 |  WINDOW SORT       |      |     5 |    45 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    45 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

使用rownum：

select ename, 
  ROWNUM
from emp
where deptno =20;

ENAME          ROWNUM
---------- ----------
SMITH               1
JONES               2
SCOTT               3
ADAMS               4
FORD                5


Execution Plan
----------------------------------------------------------
Plan hash value: 1498225739

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     5 |    45 |     3   (0)| 00:00:01 |
|   1 |  COUNT             |      |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  |     5 |    45 |     3   (0)| 00:00:01 |

- Giggs