rownum prevent query transformation

  今天研究下视图里含有rownum会导致无法view merge,条件无法推入到视图里，从而导致无法走索引等。具体过程如下：

dingjun123@ORADB> drop table t;


Table dropped.


Elapsed: 00:00:00.10
dingjun123@ORADB> create table t as select * from dba_objects;


Table created.


Elapsed: 00:00:00.25


创建索引：
dingjun123@ORADB> create index idx_t on t(object_id);


Index created.


收集统计信息：
 exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'dingjun123',tabname=>'t',
estimate_percent=>null,method_opt=>'for all columns size auto',no_invalidate=>false,cascade=>true,degree => 10);
 

 

 下面创建分别不带rownum和带rownum的视图看看会发生什么：

--不带rownum的view 
dingjun123@ORADB> create view v1_t as select * from t;


View created.


--带rownum的view
dingjun123@ORADB> create view v1_t_rownum(object_id,object_name,rn) as select object_id,object_name,rownum from t;


View created.

--简单view可以view merge谓词推入到视图里，走索引
dingjun123@ORADB> select * from v1_t where object_id = 100;


1 row selected.


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208


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


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


   2 - access("OBJECT_ID"=100)




Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1611  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


--带rownum的view,无法view merge,谓词推入不到视图里，无法走索引
dingjun123@ORADB> select * from v1_t_rownum where object_id = 100;


1 row selected.


Elapsed: 00:00:00.02


Execution Plan
----------------------------------------------------------
Plan hash value: 4233993970


-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             | 76816 |  6901K|   306   (1)| 00:00:04 |
|*  1 |  VIEW               | V1_T_ROWNUM | 76816 |  6901K|   306   (1)| 00:00:04 |
|   2 |   COUNT             |             |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T           | 76816 |  2250K|   306   (1)| 00:00:04 |
-----------------------------------------------------------------------------------


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


   1 - filter("OBJECT_ID"=100)




Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       1101  consistent gets
          0  physical reads
          0  redo size
        676  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

  带rownum的视图比较特殊（类似子查询等有rownum也一样），它会阻止查询转换（注入predicate push、subquery unnest,view merge,etc），因为不让带rownum的单独执行，可能会导致结果错误。