关于rownum的小测试

1. 问题描述

 

今天有个同事测试rownum，发现一个小问题，跟我以前的认识不一样。我一直认为rownum是一个标签，是对查询结果集进行顺序的标记。所以应该是先执行没有rownum的SQL，然后再应用rownum对结果集进行筛选。就像下面这样：

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL> create table test_rownum as select mod(rownum,4) a from dual connect by rownum < 11;

 

Table created

 

SQL> select * from test_rownum;

 

         A

----------

         1

         2

         3

         0

         1

         2

         3

         0

         1

         2

 

10 rows selected

 

SQL> select rownum rn1, x.* from test_rownum x;

 

       RN1          A

---------- ----------

         1          1

         2          2

         3          3

         4          0

         5          1

         6          2

         7          3

         8          0

         9          1

        10          2

 

10 rows selected

 

SQL> select rownum rn2, y.* from (select rownum rn1, x.* from test_rownum x) y;

 

       RN2        RN1          A

---------- ---------- ----------

         1          1          1

         2          2          2

         3          3          3

         4          4          0

         5          5          1

         6          6          2

         7          7          3

         8          8          0

         9          9          1

        10         10          2

 

10 rows selected

 

QL> select rownum rn2, y.* from (select rownum rn1, x.* from test_rownum x) y where mod(rn1,2)=0;

 

       RN2        RN1          A

---------- ---------- ----------

         1          2          2

         2          4          0

         3          6          2

         4          8          0

         5         10          2

 

但对于distinct，却是先执行rownum，然后再执行distinct的。下面是测试结果：

 

SQL> select distinct a from test_rownum;

 

         A

----------

         1

         2

         3

         0

 

SQL> select distinct a from test_rownum where rownum < 2;

 

         A

----------

         1

 

SQL> select distinct a from test_rownum where rownum < 3;

 

         A

----------

         1

         2

 

SQL> select distinct a from test_rownum where rownum < 4;

 

         A

----------

         1

         2

         3

 

SQL> select distinct a from test_rownum where rownum < 5;

 

         A

----------

         1

         2

         3

         0

 

2. 从执行计划上进行分析

 

刚才看到的是这个疑惑的问题的现象，下面从执行计划上看一下：

 

(1) 普通的SQL：只有一个全表扫描。

 

SQL> explain plan FOR

  2  SELECT x.* from test_rownum x;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2707342976

 

--------------------------------------------------------------------------------

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |             |    10 |   130 |     3   (0)| 00:00:01

|   1 |  TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:01

--------------------------------------------------------------------------------

 

Note

-----

   - dynamic sampling used for this statement

 

12 rows selected

 

(2) 查询结果中，显示rownum：此时执行计划里多了一个count计数操作

 

SQL> explain plan FOR

  2  select rownum rn1, x.* from test_rownum x;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2770385711

 

--------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |    10 |   130 |     3   (0)| 00:00:01

|   1 |  COUNT             |             |       |       |            |

|   2 |   TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:01

--------------------------------------------------------------------------------

 

Note

-----

   - dynamic sampling used for this statement

 

13 rows selected

 

(3) 多一个谓词rownum < 3：此时count计数操作中，又多了一个终止键（stopkey），即查询到rownum=3时发现不满足条件，后面的数据就不再查了。

 

SQL> explain plan FOR

  2  select rownum rn1, x.* from test_rownum x WHERE ROWNUM < 3;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 2968155833

--------------------------------------------------------------------------------

| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |             |     2 |    26 |     2   (0)| 00:00:01

|*  1 |  COUNT STOPKEY     |             |       |       |            |

|   2 |   TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     2   (0)| 00:00:01

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<3)

Note

-----

   - dynamic sampling used for this statement

18 rows selected

 

(4) 至此rownum的执行过程就比较清楚了。下面我们用两个rownum继续测试：

 

-- 内外层SQL中都多了一个count计数操作，这里的谓词 rn1 < 4 被当作了一个过滤条件。

SQL> explain plan FOR

  2  select rownum rn2, y.* from (select rownum rn1, x.* from test_rownum x) y where rn1 < 4;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3737182419

 

--------------------------------------------------------------------------------

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |    10 |   260 |     3   (0)| 00:00:

|   1 |  COUNT               |             |       |       |            |

|*  2 |   VIEW               |             |    10 |   260 |     3   (0)| 00:00:

|   3 |    COUNT             |             |       |       |            |

|   4 |     TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("RN1"<4)

 

Note

-----

   - dynamic sampling used for this statement

 

20 rows selected

 

-- 谓词换成外层SQL的rownum：这个执行计划就在预料之中了。

SQL> explain plan FOR

  2  select rownum rn2, y.* from (select rownum rn1, x.* from test_rownum x) y where ROWNUM < 4;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1119532285

 

--------------------------------------------------------------------------------

| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |             |     3 |    78 |     2   (0)| 00:00:

|*  1 |  COUNT STOPKEY       |             |       |       |            |

|   2 |   VIEW               |             |    10 |   260 |     2   (0)| 00:00:

|   3 |    COUNT             |             |       |       |            |

|   4 |     TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     2   (0)| 00:00:

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<4)

 

Note

-----

   - dynamic sampling used for this statement

 

20 rows selected

 

(5) 看看distinct的执行计划：是先执行带有终止键（stopkey）的count计数，然后再执行的distinct。

 

SQL> explain plan FOR

  2  select distinct a from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 1179436725

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     2 |    26 |     4  (25)| 00:00:0

|   1 |  HASH UNIQUE        |             |     2 |    26 |     4  (25)| 00:00:0

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

3. 测试其它的聚合函数

 

猜测可能对于聚合函数，rownum就是如此：rownum优先级大于聚合函数。于是对一些常用聚合函数的测试，发现确实如此：

 

-- 1. count

SQL> explain plan FOR

  2  select COUNT(*) from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3018287901

 

---------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     1 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE     |             |     1 |            |          |

|*  2 |   COUNT STOPKEY     |             |       |            |          |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |     3   (0)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

SQL> alter table test_rownum add b number;

 

Table altered

 

SQL> update test_rownum set b = rownum;

 

10 rows updated

 

SQL> commit;

 

Commit complete

 

SQL> select * from test_rownum;

 

         A          B

---------- ----------

         1          1

         2          2

         3          3

         0          4

         1          5

         2          6

         3          7

         0          8

         1          9

         2         10

 

10 rows selected

 

SQL> explain plan FOR

  2  select a, COUNT(b) from test_rownum where rownum < 3 GROUP BY a;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3427750650

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     2 |    52 |     4  (25)| 00:00:0

|   1 |  HASH GROUP BY      |             |     2 |    52 |     4  (25)| 00:00:0

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   260 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

-- 2. avg

SQL> explain plan FOR

  2  select AVG(a) from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3018287901

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     1 |    13 |     3   (0)| 00:00:0

|   1 |  SORT AGGREGATE     |             |     1 |    13 |            |

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

-- 3. max

SQL> explain plan FOR

  2  select MAX(a) from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3018287901

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     1 |    13 |     3   (0)| 00:00:0

|   1 |  SORT AGGREGATE     |             |     1 |    13 |            |

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

-- 4. min

SQL> explain plan FOR

  2  select MIN(a) from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3018287901

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     1 |    13 |     3   (0)| 00:00:0

|   1 |  SORT AGGREGATE     |             |     1 |    13 |            |

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

-- 5. sum

SQL> explain plan FOR

  2  select SUM(a) from test_rownum where rownum < 3;

 

Explained

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3018287901

 

--------------------------------------------------------------------------------

| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |             |     1 |    13 |     3   (0)| 00:00:0

|   1 |  SORT AGGREGATE     |             |     1 |    13 |            |

|*  2 |   COUNT STOPKEY     |             |       |       |            |

|   3 |    TABLE ACCESS FULL| TEST_ROWNUM |    10 |   130 |     3   (0)| 00:00:0

--------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(ROWNUM<3)

 

Note

-----

   - dynamic sampling used for this statement

 

19 rows selected

 

4. 结论

 

如果在同一级SQL中存在rownum和聚合函数，那么先执行rownum过滤，再应用聚合函数对结果集进行分组操作得到新的结果集。