no_merge hint

no_merge强制oracle不把子查询inline view和outer view外层查询合并为一个单一的查询,即子查询运行出结果集再和外层查询的其它结果集进行关联操作.

script

drop table t1 purge;
drop table t2 purge;

create table t1(id1 number,name varchar2(50));
create table t2(id2 number,name varchar2(50));

create index id1_t1 on t1(id1);
create index id2_t2 on t2(id2);

insert into t1 select rownum + 1,object_name from dba_objects;
insert into t2 select rownum + 2,object_name from dba_objects;

commit;

begin
    dbms_stats.gather_table_stats(
        ownname         => user,
        tabname         => 't1',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/


begin
    dbms_stats.gather_table_stats(
        ownname         => user,
        tabname         => 't2',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

select *
  from t1, (select t2.name from t2 where t2.id2 < 20) v
 where t1.id1 < 20
   and t1.name = v.name(+);

–no hint

SQL> select *
  2    from t1, (select t2.name from t2 where t2.id2 < 20) v
  3   where t1.id1 < 20
  4     and t1.name = v.name(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 466786344

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |    18 |  1044 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER             |        |    18 |  1044 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1     |    18 |   522 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ID1_T1 |    18 |       |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| T2     |    17 |   493 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | ID2_T2 |    17 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("T1"."NAME"="T2"."NAME"(+))
   3 - access("T1"."ID1"<20)
   5 - access("T2"."ID2"(+)<20)

–add hint no_merge–

SQL> select /*+ no_merge(@v1)*/*
  2    from t1, (select /*+ qb_name(v1)*/t2.name from t2 where t2.id2 < 20) v
  3   where t1.id1 < 20
  4     and t1.name = v.name(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 2487080344

----------------------------------------------------------------------------------------
| Id  | Operation                     | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |        |    18 |  1008 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER              |        |    18 |  1008 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T1     |    18 |   522 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | ID1_T1 |    18 |       |     2   (0)| 00:00:01 |
|   4 |   VIEW                        |        |    17 |   459 |     3   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| T2     |    17 |   493 |     3   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | ID2_T2 |    17 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - access("T1"."NAME"="V"."NAME"(+))
   3 - access("T1"."ID1"<20)
   6 - access("T2"."ID2"<20)
此条目发表在Uncategorized分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s