no_swap_join_inputs and swap_join_inputs hint

当oracle优化器选择hash join作为执行计划join时,如果我们希望oracle用某个表 (row source)来作为build table的情况,可以选择使用swap_join_inputs(rowsource_alias) HINT来控制.

scripts:

env 11.2.0.4
drop table t1_object purge;
drop table t2_object purge;
drop table t3_object purge;


create table t1_object(id1 number,name varchar2(50));
create table t2_object(id2 number,name varchar2(50));
create table t3_object(id3 number,name varchar2(50));

insert into t1_object select rownum,object_name from dba_objects;
insert into t2_object select rownum + 10000,object_name from dba_objects where rownum <= 500;
insert into t3_object select rownum + 10000,object_name from dba_objects where rownum <= 5000; commit;  begin           dbms_stats.gather_table_stats(                        ownname         => user,
        tabname         => 't1_object',
        cascade         => true,
        estimate_percent    => null, 
        method_opt      =>'for all columns size 1'
    );
end;
/

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

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

set autotrace trace explain
select * from table(dbms_xplan.display(format=>'advanced'));
select * from t1_object t1,t2_object t2,t3_object t3  where t1.id1 = t2.id2 and t2.name = t3.name;

–no hint–

SQL> select * from t1_object t1,t2_object t2,t3_object t3  where t1.id1 = t2.id2 and t2.name = t3.name;

Execution Plan
----------------------------------------------------------
Plan hash value: 2552126218

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   567 | 39690 |   125   (2)| 00:00:02 |
|*  1 |  HASH JOIN          |           |   567 | 39690 |   125   (2)| 00:00:02 |
|*  2 |   HASH JOIN         |           |   500 | 24000 |   118   (2)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| T2_OBJECT |   500 |  9500 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1_OBJECT | 87024 |  2464K|   115   (1)| 00:00:02 |
|   5 |   TABLE ACCESS FULL | T3_OBJECT |  5000 |   107K|     7   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("T2"."NAME"="T3"."NAME")
   2 - access("T1"."ID1"="T2"."ID2")

–add hint

SQL> select /*+ ordered use_hash(t1) swap_join_inputs(t3)*/
  2  *
  3    from t1_object t1, t2_object t2, t3_object t3
  4   where t1.id1 = t2.id2
  5     and t2.name = t3.name;

Execution Plan
----------------------------------------------------------
Plan hash value: 2589895580

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   567 | 39690 |       |   295   (1)| 00:00:04 |
|*  1 |  HASH JOIN          |           |   567 | 39690 |       |   295   (1)| 00:00:04 |
|   2 |   TABLE ACCESS FULL | T3_OBJECT |  5000 |   107K|       |     7   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |           |   500 | 24000 |  3488K|   288   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| T1_OBJECT | 87024 |  2464K|       |   115   (1)| 00:00:02 |
|   5 |    TABLE ACCESS FULL| T2_OBJECT |   500 |  9500 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   1 - access("T2"."NAME"="T3"."NAME")
   3 - access("T1"."ID1"="T2"."ID2")

ORDERED: Use the join order t1_object,t2_object,t3_object
USE_HASH(t1): Use a hash join with t1 as the build table
SWAP_JOIN_INPUTS(t3): Swap the join inputs involving t3,use the t3 as the build table.

no_swap_join_inputs(rowsource_alias): 使Oracle优化器使用这个rowsource作为probe table。

此条目发表在Hint分类目录。将固定链接加入收藏夹。

发表评论

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