两表关联只返回一个表的数据叫半连接。半连接一般就是指的in 和exists。
半连接的优化最为复杂。
半连接等价改写
in 和exists一般情况下都可以进行等价改写
半连接in的写法如下:
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select * from dept where deptno in (select deptno from emp);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
3 rows selected.
半连接exists的写法如下:
scott@orclpdb1:orclcdb> select * from dept where exists(select null from emp where dept.deptno=emp.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
3 rows selected.
scott@orclpdb1:orclcdb>
控制半连接执行计划
scott@orclpdb1:orclcdb> set autot trace;
scott@orclpdb1:orclcdb> select * from dept where deptno in (select deptno from emp);3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1090737117----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN SEMI | | 3 | 69 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0)| 00:00:01 |
|* 4 | SORT UNIQUE | | 14 | 42 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 42 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------4 - access("DEPTNO"="DEPTNO")
filter("DEPTNO"="DEPTNO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
421 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
3 rows processedscott@orclpdb1:orclcdb>
执行计划中DEPT与EMP是采用排序合并连接进行关联的。
现在让DEPT与EMP进行嵌套循环连接,同时让DEPT当驱动表。
scott@orclpdb1:orclcdb> set autot trace
scott@orclpdb1:orclcdb>
scott@orclpdb1:orclcdb> select /*+ use_nl(emp@a,dept) leading(dept)*/
2 *
from dept
4 where deptno in (select /*+qb_name(a) */
5 deptno
6 from emp);
3 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2645846736
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 69 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS SEMI | | 3 | 69 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 33 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"="DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
2 - SEL$2D5AF68E / DEPT@SEL$1
U - use_nl(emp@a,dept)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
scott@orclpdb1:orclcdb>