SWAP_JOIN_INPUTS Oracle Hint(处理hash join强制大表（segment

SWAP_JOIN_INPUTS Oracle Hint(处理hash join强制大表（segment_size大）作为被驱动表)

阅读量：6840 次

发布时间：2019-06-26

本文共 9810 字，大约阅读时间需要 32 分钟。

swap_join_inputs是针对哈希连接的hint，它的含义是让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。

注意，在swap_join_inputs hint中指定的目标表应该是原哈希连接中的被驱动表，否则oracle会忽略该hint。

/*+ swap_join_inputs(原哈希连接的被驱动表) */

其使用范例如下：

                         
select
/*+ leading(dept) use_hash(emp) swap_join_intputs(emp) */ * 
from
emp,dept 
where
emp.deptno=dept.deptno
        
      

测试案例：

                         
SCOTT@ORA12C> 
create
table
t1 
as
select
* 
from
dba_objects 
where
rownum<2;
Table
created.
SCOTT@ORA12C> 
create
table
t2 
as
select
* 
from
dba_objects 
where
rownum<12;
Table
created.
SCOTT@ORA12C> 
create
table
t3 
as
select
* 
from
dba_objects 
where
rownum<22;
Table
created.
        
      

收集统计信息：

                         
SCOTT@ORA12C> 
exec
dbms_stats.gather_table_stats(ownname => 
'SCOTT'
,tabname => 
'T1'
,estimate_percent => 100,
cascade
=> 
true
,method_opt => 
'for all columns size 1'
,no_invalidate => 
false
);
PL/SQL 
procedure
successfully completed.
SCOTT@ORA12C> 
exec
dbms_stats.gather_table_stats(ownname => 
'SCOTT'
,tabname => 
'T2'
,estimate_percent => 100,
cascade
=> 
true
,method_opt => 
'for all columns size 1'
,no_invalidate => 
false
);
PL/SQL 
procedure
successfully completed.
SCOTT@ORA12C> 
exec
dbms_stats.gather_table_stats(ownname => 
'SCOTT'
,tabname => 
'T3'
,estimate_percent => 100,
cascade
=> 
true
,method_opt => 
'for all columns size 1'
,no_invalidate => 
false
);
PL/SQL 
procedure
successfully completed.
        
      

3个表的记录如下：

                         
SCOTT@ORA12C> 
select
count
(*) 
from
t1;
 
COUNT
(*)
-----------------
1
1 row selected.
SCOTT@ORA12C> 
select
count
(*) 
from
t2;
 
COUNT
(*)
-----------------
       
11
1 row selected.
SCOTT@ORA12C> 
select
count
(*) 
from
t3;
 
COUNT
(*)
-----------------
       
21
1 row selected.
        
      

现在我们来让表T2和T3做哈希连接，由于T3表的记录数比T2表的记录数多，所以这里指定T3为哈希连接的被驱动表：

                         
select
/*+ ordered use_hash(t3) */ t2.object_name,t3.object_type
  
2  
from
t2,t3 
where
t2.object_id=t3.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1730954469
---------------------------------------------------------------------------
| Id  | Operation   | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
---------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT   |  |    11 |   220 |6   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|  |    11 |   220 |6   (0)| 00:00:01 |
|   2 |   
TABLE
ACCESS 
FULL
| T2   |    11 |   110 |3   (0)| 00:00:01 |
|   3 |   
TABLE
ACCESS 
FULL
| T3   |    21 |   210 |3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
)
        
      

可以看到，上述SQL的执行计划现在走的是哈希连接，并且被驱动表示表T3.

如果我们想让哈希连接的被驱动表由T3变成T2，可以在上述sql加入swap_join_inputs hint：

                         
select
/*+ ordered use_hash(t3) swap_join_inputs(t3) */ t2.object_name,t3.object_type
  
2  
from
t2,t3 
where
t2.object_id=t3.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1723280936
---------------------------------------------------------------------------
| Id  | Operation   | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
---------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT   |  |    11 |   220 |6   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|  |    11 |   220 |6   (0)| 00:00:01 |
|   2 |   
TABLE
ACCESS 
FULL
| T3   |    21 |   210 |3   (0)| 00:00:01 |
|   3 |   
TABLE
ACCESS 
FULL
| T2   |    11 |   110 |3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
)
        
      

用leading(t3) use_hash(t2)也可以同样达到目的：

                         
select
/*+ leading(t3) use_hash(t2) */ t2.object_name,t3.object_type
  
2  
from
t2,t3 
where
t2.object_id=t3.object_id;
Execution Plan
----------------------------------------------------------
Plan hash value: 1723280936
---------------------------------------------------------------------------
| Id  | Operation   | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
---------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT   |  |    11 |   220 |6   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|  |    11 |   220 |6   (0)| 00:00:01 |
|   2 |   
TABLE
ACCESS 
FULL
| T3   |    21 |   210 |3   (0)| 00:00:01 |
|   3 |   
TABLE
ACCESS 
FULL
| T2   |    11 |   110 |3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
)
        
      

由此可见在两个表关联的时候，可以用其他hint代替swap_join_inputs来达到相同的目的：

那么多表关联呢：

                         
select
/*+ ordered use_hash(t3) */ t1.owner,t2.object_name,t3.object_type
  
2  
from
t2,t3,t1 
where
t2.object_id=t3.object_id 
and
t1.object_type=t3.object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 98820498
----------------------------------------------------------------------------
| Id  | Operation    | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
----------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT    |   | 4 |   120 | 9   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|   | 4 |   120 | 9   (0)| 00:00:01 |
|*  2 |   HASH 
JOIN
|   |11 |   220 | 6   (0)| 00:00:01 |
|   3 |    
TABLE
ACCESS 
FULL
| T2   |11 |   110 | 3   (0)| 00:00:01 |
|   4 |    
TABLE
ACCESS 
FULL
| T3   |21 |   210 | 3   (0)| 00:00:01 |
|   5 |   
TABLE
ACCESS 
FULL
| T1   | 1 |10 | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T1"
.
"OBJECT_TYPE"
=
"T3"
.
"OBJECT_TYPE"
)
   
2 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
        
      

可以看到，现在上述sql的执行计划是先由表T2和表T3做哈希连接，然后将他们做哈希连接的连接结果集再和表T1做一次哈希连接。

表T1的记录数为1，表T2的记录数为11，表T3的记录数为21，所以当表的T2和T3做哈希连接时，记录数多的表T3应该是被驱动表，这是因为我们在上述sql中使用了ordered hint和use_hash HINT指定表T3作为表T2和T3连接的时的被驱动表，所以oracle这里选择了表T2和T3做哈希连接，并且选择了表T3作为该哈希连接的被驱动表，这是没有问题的，现在问题在于表T1的记录数仅为1，所以当表T2和T3做哈希连接的结果再和表T1做哈希连接时，表T1应该是驱动表，而不是在上述执行计划里显示的那样作为第二个哈希连接的被驱动表。

使用下面HINT:

                         
select
/*+ ordered use_hash(t3) */ t1.owner,t2.object_name,t3.object_type
  
2  
from
t1,t2,t3 
where
t2.object_id=t3.object_id 
and
t1.object_type=t3.object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 38266800
------------------------------------------------------------------------------
| Id  | Operation      | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
------------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT      |      |   4 | 120 |   9   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|      |   4 | 120 |   9   (0)| 00:00:01 |
|   2 |   MERGE 
JOIN
CARTESIAN|      |  11 | 220 |   6   (0)| 00:00:01 |
|   3 |    
TABLE
ACCESS 
FULL
| T1   |   1 |  10 |   3   (0)| 00:00:01 |
|   4 |    BUFFER SORT      |      |  11 | 110 |   3   (0)| 00:00:01 |
|   5 |     
TABLE
ACCESS 
FULL
| T2   |  11 | 110 |   3   (0)| 00:00:01 |
|   6 |   
TABLE
ACCESS 
FULL
| T3   |  21 | 210 |   3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
AND
      
"T1"
.
"OBJECT_TYPE"
=
"T3"
.
"OBJECT_TYPE"
)
        
      

                         
select
/*+ leading(t1) use_hash(t3) */ t1.owner,t2.object_name,t3.object_type
  
2  
from
t1,t2,t3 
where
t2.object_id=t3.object_id 
and
t1.object_type=t3.object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 2308542799
----------------------------------------------------------------------------
| Id  | Operation    | 
Name
| 
Rows
| Bytes | Cost (%CPU)| 
Time
|
----------------------------------------------------------------------------
|   0 | 
SELECT
STATEMENT    |   | 7 |   210 | 9   (0)| 00:00:01 |
|*  1 |  HASH 
JOIN
|   | 7 |   210 | 9   (0)| 00:00:01 |
|*  2 |   HASH 
JOIN
|   | 7 |   140 | 6   (0)| 00:00:01 |
|   3 |    
TABLE
ACCESS 
FULL
| T1   | 1 |10 | 3   (0)| 00:00:01 |
|   4 |    
TABLE
ACCESS 
FULL
| T3   |21 |   210 | 3   (0)| 00:00:01 |
|   5 |   
TABLE
ACCESS 
FULL
| T2   |11 |   110 | 3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified 
by
operation id):
---------------------------------------------------
   
1 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
)
   
2 - access(
"T1"
.
"OBJECT_TYPE"
=
"T3"
.
"OBJECT_TYPE"
)
        
      