sql - 甲骨文 : ROW_NUMBER 20 times faster than ROWNUM

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;

Answer 1

如前所述，ROW_NUMBER() 和 ROWNUM 根本不等同。

第一个是使用窗口的解析函数。 order by command指定顺序。

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 |
---------------------------------------------------------------------------

使用行数:

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 |