no_merge的含义

create table wxh_tbd1 as select * from dba_objects;
create table wxh_tbd2 as select * from dba_objects;

create index ind_oi on wxh_tbd1(object_name);
create index t_22 on wxh_tbd1(object_id);
create index t_23 on wxh_tbd2(object_id);

create view v_tbd2
as
select a.object_id,a.object_name,b.object_type from wxh_tbd1 a,wxh_tbd2 b
where a.object_id=b.object_id;

select /*+ no_merge(d) */* from wxh_tbd1 c,v_tbd2 d
where c.object_id=d.object_id and c.object_name='WXH_TBD';

select * from wxh_tbd1 c,v_tbd2 d
where c.object_id=d.object_id and c.object_name='WXH_TBD';

 

-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |     1 |   131 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                  |          |       |       |            |          |
|   2 |   NESTED LOOPS                 |          |     1 |   131 |     6   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                |          |     1 |   118 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     1 |    94 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IND_OI   |     1 |       |     1   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     1 |    24 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | T_22     |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | T_23     |     1 |       |     1   (0)| 00:00:01 |
|   9 |   TABLE ACCESS BY INDEX ROWID  | WXH_TBD2 |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         36  consistent gets
         21  physical reads
          0  redo size
       2138  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |     1 |   184 |   132   (2)| 00:00:02 |
|*  1 |  HASH JOIN                   |          |     1 |   184 |   132   (2)| 00:00:02 |
|   2 |   TABLE ACCESS BY INDEX ROWID| WXH_TBD1 |     1 |    94 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_OI   |     1 |       |     1   (0)| 00:00:01 |
|   4 |   VIEW                       | V_TBD2   | 20082 |  1765K|   130   (2)| 00:00:02 |
|*  5 |    HASH JOIN                 |          | 20082 |   725K|   130   (2)| 00:00:02 |
|   6 |     TABLE ACCESS FULL        | WXH_TBD2 | 20151 |   255K|    64   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | WXH_TBD1 | 20151 |   472K|    64   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
        568  consistent gets
        552  physical reads
          0  redo size
       2138  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-703648/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-703648/