view merging，在有rownum情况下不使用的原因！
创建了2个VIEW，一个使用了ROWNUM。使用ROWNUM后不会view merging了。导致全表扫描了。
SQL> create or replace force view MICS_APP.VIEWTEST (OBJECT_NAME) as select OBJECT_NAME from test;
View created.
SQL> create or replace force view MICS_APP.VIEWTEST_ROWNUM (NUM,OBJECT_NAME) as select ROWNUM,TEST.OBJECT_NAME from test;
View created.
SQL> select * from MICS_APP.VIEWTEST where OBJECT_NAME='I_OID1';
Execution Plan
----------------------------------------------------------
Plan hash value: 3357096749
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TEST_IND | 2 | 50 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='I_OID1')
SQL> select * FROM MICS_APP.VIEWTEST_ROWNUM A where OBJECT_NAME='I_OID1';
Execution Plan
----------------------------------------------------------
Plan hash value: 484067762
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7900 | 2 (0)| 00:00:01 |
|* 1 | VIEW | VIEWTEST_ROWNUM | 100 | 7900 | 2 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 100 | 2500 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='I_OID1')
使用HINT也无法避免：
SQL> select /*+ merge(MICS_APP.VIEWTEST_ROWNUM) */ * FROM MICS_APP.VIEWTEST_ROWNUM A where OBJECT_NAME='I_OID1';
Execution Plan
----------------------------------------------------------
Plan hash value: 484067762
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 7900 | 2 (0)| 00:00:01 |
|* 1 | VIEW | VIEWTEST_ROWNUM | 100 | 7900 | 2 (0)| 00:00:01 |
| 2 | COUNT | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 100 | 2500 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='I_OID1')
如果跳过VIEW，也使用ROWNUM，可以使用到索引：
SQL> select ROWNUM,TEST.OBJECT_NAME from MICS_APP.test where OBJECT_NAME='I_OID1';
Execution Plan
----------------------------------------------------------
Plan hash value: 133926536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 50 | 1 (0)| 00:00:01 |
| 1 | COUNT | | | | | |
|* 2 | INDEX RANGE SCAN| TEST_IND | 2 | 50 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_NAME"='I_OID1')
不使用view merging的原因是，如果使用了，会导致ROWNUM的结果不同。
SQL> select * from MICS_APP.VIEWTEST_rownum where OBJECT_NAME='I_OID1';
NUM OBJECT_NAME
---------- --------------------
177 I_OID1
SQL> select ROWNUM,TEST.OBJECT_NAME from MICS_APP.test where OBJECT_NAME='I_OID1';
ROWNUM OBJECT_NAME
---------- --------------------
1 I_OID1
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/26655292/viewspace-750639/ ，如需转载，请注明出处，否则将追究法律责任。
