在查询中有时使用到伪列rownum对使用伪列rownum的查询,优化器要么使用count操作,要么使用count stopkey操作来对rownum计数器进行增量(注意:这里的count操作和count stopkey操作与count函数没有任何关系),如果对rownum伪列应用一个限定条件,如:where rownum<10;则使用 count stopkey操作,如果不为Rownum伪列指定限定条件,则是使用count操作。
例1:不在Rownum伪列上使用限定条件
SQL> explain plan for select employee_id,rownum from hr.employees;
SQL> set linesize 200
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3852095639
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
| 2 | INDEX FULL SCAN| EMP_EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
SQL> select employee_id from hr.employees where rownum<10;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3852095639
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 428 | 1 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
| 2 | INDEX FULL SCAN| EMP_EMP_ID_PK | 107 | 428 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------
9 rows selected.