下面是测试在HASH连接的时候，修改驱动表的顺序。HASH连接不能用于不等值连接。
1 环境介绍
|
www.cdhtz.com > 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 www.cdhtz.com > !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 创建测试表
|
www.cdhtz.com > create table htz.htz1 as select * from dba_objects; Table created. www.cdhtz.com > create table htz.htz2 as select * from dba_objects; Table created. www.cdhtz.com > create table htz.htz3 as select * from dba_objects; Table created. |
3 原SQL语句执行计划
|
www.cdhtz.com > 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 www.cdhtz.com > @find_sql Enter value for sql_text: huang Enter value for sql_id: SQL_ID 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 www.cdhtz.com > set lines 200 www.cdhtz.com > 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
|
www.cdhtz.com > 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
www.cdhtz.com > @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
www.cdhtz.com > 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中的所有提示都将被忽略。
SWAP_JOIN_INPUTS 修改HASH连接的驱动表：等您坐沙发呢！