swap_join_inputs是针对哈希连接的hint，它的含义是让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。
注意，在swap_join_inputs hint中指定的目标表应该是原哈希连接中的被驱动表，否则oracle会忽略该hint。
/*+ swap_join_inputs(原哈希连接的被驱动表) */
其使用范例如下：
|
1
2
|
select
/*+ leading(dept) use_hash(emp) swap_join_intputs(emp) */ *
from
emp,dept
where
emp.deptno=dept.deptno
|
测试案例：
|
1
2
3
4
5
6
|
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.
|
收集统计信息：
|
1
2
3
4
5
6
|
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个表的记录如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
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为哈希连接的被驱动表：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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)也可以同样达到目的：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
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来达到相同的目的：
那么多表关联呢：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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"
)
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
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"
)
|
加入以下hint，就解决:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
SELECT
/*+ ordered use_hash(t3) swap_join_inputs(t1) */
t1.owner, t2.object_name, t3.object_type
FROM
t2, t3, t1
WHERE
t2.object_id = t3.object_id
5
AND
t1.object_type = t3.object_type;
Execution Plan
----------------------------------------------------------
Plan hash value: 3071514789
----------------------------------------------------------------------------
| 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 |
TABLE
ACCESS
FULL
| T1 | 1 |10 | 3 (0)| 00:00:01 |
|* 3 | HASH
JOIN
| |11 | 220 | 6 (0)| 00:00:01 |
| 4 |
TABLE
ACCESS
FULL
| T2 |11 | 110 | 3 (0)| 00:00:01 |
| 5 |
TABLE
ACCESS
FULL
| T3 |21 | 210 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified
by
operation id):
---------------------------------------------------
1 - access(
"T1"
.
"OBJECT_TYPE"
=
"T3"
.
"OBJECT_TYPE"
)
3 - access(
"T2"
.
"OBJECT_ID"
=
"T3"
.
"OBJECT_ID"
)
|
转：http://7642644.blog.51cto.com/7632644/1699902
文章可以转载，必须以链接形式标明出处。
本文转自 张冲andy 博客园博客，原文链接： http://www.cnblogs.com/andy6/p/6790184.html
，如需转载请自行联系原作者
