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博客 ” ，链接：http://blog.itpub.net/26655292/viewspace-750639/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26655292/viewspace-750639/