关于视图中使用rownum对执行计划的影响

最近发现在视图中使用rownum伪列会使执行改变，可能导致无法正常使用索引，以后要注意rownum的使用了，呵呵

 

现象如下：

SQL> create table test as
2 select * from dba_objects;

Table created.

SQL> create index ix_test_object_name on test(object_name);

Index created.

SQL> set autotrace on
SQL> select * from test t
2 where t.object_name='EMP';

Execution Plan
----------------------------------------------------------
Plan hash value: 3243212241

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 177 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 177 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TEST_OBJECT_NAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

可以正常使用索引

SQL> create view v_test
2 as
3 select rownum as rid,
4 t.object_id,
5 t.object_name
6 from test t;

View created.

SQL> select * from v_test t
2 where t.object_name='EMP';

Execution Plan
----------------------------------------------------------
Plan hash value: 3564173094

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44356 | 3985K| 156 (1)| 00:00:02 |
|* 1 | VIEW | V_TEST | 44356 | 3985K| 156 (1)| 00:00:02 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 44356 | 3421K| 156 (1)| 00:00:02 |
------------------------------------------------------------------------------

全表扫描了

但如果不用rownum

SQL> create view v_test1
2 as
3 select 1 as rid,
4 t.object_id,
5 t.object_name
6 from test t;

View created.

SQL> select * from v_test1 t
2 where t.object_name='EMP';

Execution Plan
----------------------------------------------------------
Plan hash value: 3243212241

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 79 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TEST_OBJECT_NAME | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

这样就可以正常使用索引了，呵呵，至于为什么会这样，请读者自己考虑吧。