no_swap_join_inputs 与 leading

来源：互联网发布：双轨直销系统源码编辑：程序博客网时间：2024/06/05 10:40

首先建立测试用表如下

create table t1 as select * from dba_objects;create table t2 as select * from dba_objects;create table t3 as select * from dba_objects;create table t4 as select * from dba_objects;

如下语句用leading改驱动表无效

SELECT /*+ leading(t1) use_hash(t1 t2)  use_hash(t1 t3) */ t1.object_id,t2.object_name,t3.object_name  FROM t1 INNER JOIN t2 ON (t1.object_id = t2.object_id) INNER JOIN t3 ON (t1.object_name = t3.object_name)Execution Plan----------------------------------------------------------Plan hash value: 1573120526------------------------------------------------------------------------------------| Id  | Operation    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   |  1218K|   260M|   |  2841   (1)| 00:00:35 ||*  1 |  HASH JOIN    |   |  1218K|   260M|  8656K|  2841   (1)| 00:00:35 ||   2 |   TABLE ACCESS FULL | T3   |   113K|  7320K|   |   347   (1)| 00:00:05 ||*  3 |   HASH JOIN    |   | 77853 |11M|  8576K|  1445   (1)| 00:00:18 ||   4 |    TABLE ACCESS FULL| T1   | 96439 |  7440K|   |   347   (1)| 00:00:05 ||   5 |    TABLE ACCESS FULL| T2   | 77853 |  6006K|   |   347   (1)| 00:00:05 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")   3 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note-----   - dynamic sampling used for this statement (level=2)

这时可以与no_swap_join_inputs配合

SELECT /*+ no_swap_join_inputs(t3) leading(t1) use_hash(t1 t2)  use_hash(t1 t3) */ t1.object_id,t2.object_name,t3.object_name  FROM t1 INNER JOIN t2 ON (t1.object_id = t2.object_id) INNER JOIN t3 ON (t1.object_name = t3.object_name);Execution Plan----------------------------------------------------------Plan hash value: 261998084------------------------------------------------------------------------------------| Id  | Operation    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   |  1218K|   260M|   |  2841   (1)| 00:00:35 ||*  1 |  HASH JOIN    |   |  1218K|   260M|12M|  2841   (1)| 00:00:35 ||*  2 |   HASH JOIN    |   | 77853 |11M|  8576K|  1445   (1)| 00:00:18 ||   3 |    TABLE ACCESS FULL| T1   | 96439 |  7440K|   |   347   (1)| 00:00:05 ||   4 |    TABLE ACCESS FULL| T2   | 77853 |  6006K|   |   347   (1)| 00:00:05 ||   5 |   TABLE ACCESS FULL | T3   |   113K|  7320K|   |   347   (1)| 00:00:05 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note-----   - dynamic sampling used for this statement (level=2)

SELECT /*+ no_swap_join_inputs(t3) leading(t2) use_hash(t1 t2)  use_hash(t1 t3) */ t1.object_id,t2.object_name,t3.object_name  FROM t1 INNER JOIN t2 ON (t1.object_id = t2.object_id) INNER JOIN t3 ON (t1.object_name = t3.object_name);Execution Plan----------------------------------------------------------Plan hash value: 1184213596------------------------------------------------------------------------------------| Id  | Operation    | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time   |------------------------------------------------------------------------------------|   0 | SELECT STATEMENT    |   |  1218K|   260M|   |  2841   (1)| 00:00:35 ||*  1 |  HASH JOIN    |   |  1218K|   260M|12M|  2841   (1)| 00:00:35 ||*  2 |   HASH JOIN    |   | 77853 |11M|  6920K|  1445   (1)| 00:00:18 ||   3 |    TABLE ACCESS FULL| T2   | 77853 |  6006K|   |   347   (1)| 00:00:05 ||   4 |    TABLE ACCESS FULL| T1   | 96439 |  7440K|   |   347   (1)| 00:00:05 ||   5 |   TABLE ACCESS FULL | T3   |   113K|  7320K|   |   347   (1)| 00:00:05 |------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("T1"."OBJECT_NAME"="T3"."OBJECT_NAME")   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")Note-----   - dynamic sampling used for this statement (level=2)

记之

0 0