oracle swap_join_inputs,【学习笔记】如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表

【学习笔记】如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表

时间:2016-11-03 21:25   来源:Oracle研究中心   作者:HTZ   点击:

次

天萃荷净

Oracle研究中心学习笔记：分享一篇关于修改HASH连接的驱动表笔记，通过如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表详细过程笔记。

下面是测试在HASH连接的时候，修改驱动表的顺序。HASH连接不能用于不等值连接。

1 环境介绍

oracleplus.net> select * from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

oracleplus.net> !uname -a

Linux orcl9i 2.6.9-89.EL #1 Mon Apr 20 10:22:29 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux

2 创建测试表

oracleplus.net> create table htz.htz1 as select * from dba_objects;

Table created.

oracleplus.net> create table htz.htz2 as select * from dba_objects;

Table created.

oracleplus.net> create table htz.htz3 as select * from dba_objects;

Table created.

3 原SQL语句执行计划

oracleplus.net> select /*+ huang*/count(*)

2 from htz.htz1 a, htz.htz2 b, htz.htz3 c

3 where a.object_id = b.object_id

4 and a.object_id = c.object_id;

COUNT(*)

———-

74656

oracleplus.net> @find_sql

Enter value for sql_text: huang

Enter value for sql_id:

SQL_ID Oracle о CHILD HASH_VALUE PLAN_HASH EXECS ETIME AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

——————————————————————————————

7uyt5873uq4av 0 3349877083 283048524 1 .09 .09 SYS

select /*+ huang*/count(*) from htz.htz1 a, htz.htz2 b, htz.htz3 c where a.object_id = b.object_id and a.object_id = c.object_id

oracleplus.net> set lines 200

oracleplus.net> select * from table(dbms_xplan.display_cursor(‘7uyt5873uq4av’,null,’advanced’));

PLAN_TABLE_OUTPUT

——————————————————————————————

SQL_ID 7uyt5873uq4av, child number 0

————————————-

select /*+ huang*/count(*) from htz.htz1 a, htz.htz2 b, htz.htz3 c

where a.object_id = b.object_id and a.object_id = c.object_id

Plan hash value: 283048524

————————————————————————————-

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

————————————————————————————-

| 0 | SELECT STATEMENT | | | | | 1242 (100)| |

| 1 | SORT AGGREGATE | | 1 | 39 | | | |

|* 2 | HASH JOIN | | 61296 | 2334K| 1704K| 1242 (1)| 00:00:15 |

| 3 | TABLE ACCESS FULL | HTZ3 | 69661 | 884K| | 298 (1)| 00:00:04 |

|* 4 | HASH JOIN | | 61296 | 1556K| 1504K| 750 (1)| 00:00:09 |

| 5 | TABLE ACCESS FULL| HTZ1 | 61295 | 778K| | 298 (1)| 00:00:04 |

| 6 | TABLE ACCESS FULL| HTZ2 | 66602 | 845K| | 298 (1)| 00:00:04 |

————————————————————————————-

Query Block Name / Object Alias (identified by operation id):

————————————————————-

1 – SEL$1

3 – SEL$1 / C@SEL$1

5 – SEL$1 / A@SEL$1

6 – SEL$1 / B@SEL$1

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)

DB_VERSION(‘11.2.0.3’)

OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)

OPT_PARAM(‘_optimizer_use_feedback’ ‘false’)

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

FULL(@”SEL$1″ “A”@”SEL$1”)

FULL(@”SEL$1″ “B”@”SEL$1”)

FULL(@”SEL$1″ “C”@”SEL$1”)

LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

USE_HASH(@”SEL$1″ “B”@”SEL$1”)

USE_HASH(@”SEL$1″ “C”@”SEL$1”)

SWAP_JOIN_INPUTS(@”SEL$1″ “C”@”SEL$1”)

END_OUTLINE_DATA

*/

4 SWAP_JOIN_INPUTS更改驱动表

手动指定表的访问顺序为A,B,C，A为驱动表，AB的结果集再去驱动C

oracleplus.net> alter system flush shared_pool;

System altered.

这里直接COPY Outline Data中的提示信息来修改就可以了。

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1″) SWAP_JOIN_INPUTS(@”SEL$1” “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1” “C”@”SEL$1”) */

count(*)

from htz.htz1 a, htz.htz2 b, htz.htz3 c

where a.object_id = b.object_id

5 and a.object_id = c.object_id;

COUNT(*)

———-

74656

oracleplus.net> @find_sql.sql

Enter value for sql_text: LEADING

Enter value for sql_id:

SQL_ID CHILD HASH_VALUE PLAN_HASH EXECS ETIME AVG_ETIME USERNAME

————- —— ———- ———- ———- ————- ————- ————-

SQLTEXT

———————————————————————————————–

87cu5q9nkqgzk 0 1764442098 274524172 1 .09 .09 SYS

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1″) SWAP_JOIN_INPUTS(@”SEL$1” “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1” “C”@”SEL$1”) */ count(*) from htz.htz1 a, htz.htz2 b,

htz.htz3 c where a.object_id = b.object_id and a.object_id = c.object_id

oracleplus.net> select * from table(dbms_xplan.display_cursor(’87cu5q9nkqgzk’,null,’advanced’));

PLAN_TABLE_OUTPUT

———————————————————————————————–

SQL_ID 87cu5q9nkqgzk, child number 0

————————————-

select /*+ LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

SWAP_JOIN_INPUTS(@”SEL$1″ “A”@”SEL$1″) NO_SWAP_JOIN_INPUTS(@”SEL$1”

“C”@”SEL$1”) */ count(*) from htz.htz1 a, htz.htz2 b, htz.htz3 c

where a.object_id = b.object_id and a.object_id = c.object_id

Plan hash value: 274524172

————————————————————————————-

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

————————————————————————————-

| 0 | SELECT STATEMENT | | | | | 1242 (100)| |

| 1 | SORT AGGREGATE | | 1 | 39 | | | |

|* 2 | HASH JOIN | | 61296 | 2334K| 2280K| 1242 (1)| 00:00:15 |

|* 3 | HASH JOIN | | 61296 | 1556K| 1504K| 750 (1)| 00:00:09 |

| 4 | TABLE ACCESS FULL| HTZ1 | 61295 | 778K| | 298 (1)| 00:00:04 |

| 5 | TABLE ACCESS FULL| HTZ2 | 66602 | 845K| | 298 (1)| 00:00:04 |

| 6 | TABLE ACCESS FULL | HTZ3 | 69661 | 884K| | 298 (1)| 00:00:04 |

————————————————————————————-

Query Block Name / Object Alias (identified by operation id):

————————————————————-

1 – SEL$1

4 – SEL$1 / A@SEL$1

5 – SEL$1 / B@SEL$1

6 – SEL$1 / C@SEL$1

Outline Data

————-

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3’)

DB_VERSION(‘11.2.0.3’)

OPT_PARAM(‘_b_tree_bitmap_plans’ ‘false’)

OPT_PARAM(‘_optimizer_use_feedback’ ‘false’)

ALL_ROWS

OUTLINE_LEAF(@”SEL$1″)

FULL(@”SEL$1″ “A”@”SEL$1”)

FULL(@”SEL$1″ “B”@”SEL$1”)

FULL(@”SEL$1″ “C”@”SEL$1”)

LEADING(@”SEL$1″ “A”@”SEL$1” “B”@”SEL$1” “C”@”SEL$1”)

USE_HASH(@”SEL$1″ “B”@”SEL$1”)

USE_HASH(@”SEL$1″ “C”@”SEL$1”)

END_OUTLINE_DATA

*/

Predicate Information (identified by operation id):

—————————————————

2 – access(“A”.”OBJECT_ID”=”C”.”OBJECT_ID”)

3 – access(“A”.”OBJECT_ID”=”B”.”OBJECT_ID”)

Column Projection Information (identified by operation id):

———————————————————–

1 – (#keys=0) COUNT(*)[22]

2 – (#keys=1)

3 – (#keys=1) “A”.”OBJECT_ID”[NUMBER,22]

4 – “A”.”OBJECT_ID”[NUMBER,22]

5 – “B”.”OBJECT_ID”[NUMBER,22]

6 – “C”.”OBJECT_ID”[NUMBER,22]

如果提示没有生效，请注意_optimizer_ignore_hints参数是否配置成TRUE。如果配置成true，那么SQL中的所有提示都将被忽略。

本文固定链接: http://www.htz.pw/2014/11/09/swap_join_inputs-%e4%bf%ae%e6%94%b9hash%e8%bf%9e%e6%8e%a5%e7%9a%84%e9%a9%b1%e5%8a%a8%e8%a1%a8.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表