/+ use_hash(t) */ --t作为被驱动表,外表 swap_join_inputs是针对哈希连接的hint，让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。leading/orderd可代替 swap_join_inputs(emp@a) swap_join_inputs(原被驱动表-) --hash jion,小表作为驱动表,控制连接顺序 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno and d.loc='BOSTON'; select * from dept d,emp e where d.deptno=e.deptno; select /*+ leading(d) use_hash(e) */ * from dept d,emp e where d.deptno=e.deptno; Execution Plan ---------------------------------------------------------- Plan hash value: 615168685 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5 | 250 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN | | 5 | 250 | 7 (15)| 00:00:01 | |* 2 | TABLE ACCESS FULL| DEPT | 1 | 30 | 3 (0)| 00:00:01 | -->驱动表(outer table) | 3 | TABLE ACCESS FULL| EMP | 14 | 280 | 3 (0)| 00:00:01 | -->e被驱动表 --------------------------------------------------------------------------- ============ Plan Table ============ --------------------------------------+-----------------------------------+ | Id | Operation | Name | Rows | Bytes | Cost | Time | --------------------------------------+-----------------------------------+ | 0 | SELECT STATEMENT | | | | 7 | | | 1 | HASH JOIN | | 14 | 1638 | 7 | 00:00:01 | | 2 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 | 00:00:01 | | 3 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 | 00:00:01 | --------------------------------------+-----------------------------------+ select /*+ leading(e) use_hash(d) swap_join_inputs(e)*/ * from dept d,emp e where d.deptno=e.deptno(+); -- SQL> select /*+ use_hash(dept,emp@a) leading(emp@a) */ * from dept where not exists (select /*+ qb_name(a) */ null from emp where emp.deptno = dept.deptno); 2 3 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 474461924 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN ANTI | | 4 | 172 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL| DEPT | 4 | 120 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- ========================================================================================== select /*+ use_hash(dept,emp@a) swap_join_inputs(emp@a) leading(emp@a) */ * from dept where not exists (select /*+ qb_name(a) */ null from emp where emp.deptno = dept.deptno); --- SQL> select /*+ use_hash(dept,emp@a) swap_join_inputs(emp@a) leading(emp@a) */ * from dept where not exists (select /*+ qb_name(a) */ null from emp where emp.deptno = dept.deptno); 2 3 Elapsed: 00:00:00.42 Execution Plan ---------------------------------------------------------- Plan hash value: 152508289 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 7 (15)| 00:00:01 | |* 1 | HASH JOIN RIGHT ANTI| | 4 | 172 | 7 (15)| 00:00:01 | | 2 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL | DEPT | 4 | 120 | 3 (0)| 00:00:01 | -----------------------------------------------------------------------------
