我们需要查看带有和不带有rownum条件的查询的执行计划。但是作为一个例子，添加一个“rownum”可以改变一个执行计划

SQL> create table t as select * from dba_objects
  2  where object_id is not null;

Table created.

SQL>
SQL> create index ix on t ( object_id );

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where object_id > 0 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 82262 |    10M|   445   (2)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 82262 |    10M|   445   (2)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_ID">0)

SQL> select * from t where object_id > 0  and rownum < 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 658510075

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     9 |  1188 |     3   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |      |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     9 |  1188 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IX   |       |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

这是一个过于简单的示例，但是您可以通过join等获得类似的结果，特别是“rownum”子句可能禁止将最里面的join折叠到最外面的join中，从而产生不同的计划。