Oracle CBO Semi Join（半连接）

半连接的定义

A semi-join is a join between two sets of data (tables) where rows from the first set are returned, based

on the presence or absence of at least one matching row in the other set.

[@more@]

半连接的定义

A semi-join is a join between two sets of data (tables) where rows from the first set are returned, based

on the presence or absence of at least one matching row in the other set.

The main difference between a normal inner join

and a semi-join is that with a semi-join, each record in the first set (OutQuery) is returned

only once, regardless of how many matches there are in the second set (Subquery).

This definition implies that the actual processing of the query can be optimized by stopping Subquery as soon

as the first match is found.这一点非常重要。

Query1和Query2执行计划中连接的方式是nested loop semi，想象dept是outer table，emp是inner loop，当在inner loop里面发现一条与外表相匹配的数据就终止inner loop。

在CBO中当包含在IN或者EXIST中的子查询可以转换成table join的方式来处理。

我们来看个例子就明白了。

Query1

select department_name

from hr.departments dept

where department_id in (select department_id from hr.employees emp);

HR@lipei> select*from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

Plan hash value: 2605691773

----------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 190 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS SEMI | | 10 | 190 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

Query2

select department_name

from hr.departments dept

where exists (select 1 from hr.employees emp

where emp.department_id = dept.department_id);

HR@lipei> select*from table(dbms_xplan.display(null,null,'advanced'));

PLAN_TABLE_OUTPUT

-------------------------------------------------------------------------------------------

Plan hash value: 2605691773

----------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10 | 190 | 3 (0)| 00:00:01 |

| 1 | NESTED LOOPS SEMI | | 10 | 190 | 3 (0)| 00:00:01 |

| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 41 | 123 | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

3 - access("EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID")

通过以上可以看到总之In和Exists可以做不同形式的查询转换，而且在一般情况下是没有区别的。

HR@lipei> alter session set events '10053 trace name context forever,level 1';

抓取10053看一下这两个查询：

****************

QUERY BLOCK TEXT

****************

select /* using in */ department_name

from hr.departments dept

where department_id IN (select department_id from hr.employees emp)

*****************************

Cost-Based Subquery Unnesting

*****************************

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

Subquery removal for query block SEL$2 (#2)

RSW: Not valid for subquery removal SEL$2 (#2)

Subquery unchanged.

Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not require

costing.

SU: Considering subquery unnest on query block SEL$1 (#1).

SU: Checking validity of unnesting subquery SEL$2 (#2)

SU: Passed validity checks.

SU: Transforming ANY subquery to a join.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "DEPT"."DEPARTMENT_NAME" "DEPARTMENT_NAME" FROM "HR"."EMPLOYEES"

"EMP","HR"."DEPARTMENTS" "DEPT" WHERE "DEPT"."DEPARTMENT_ID"="EMP"."DEPARTMENT_ID"

Listing 11-13. Excerpts from 10053 trace for EXISTS version

****************

QUERY BLOCK TEXT

****************

select /* using exists */ department_name

from hr.departments dept

where EXISTS (select null from hr.employees emp

where emp.department_id = dept.department_id)

*****************************

Cost-Based Subquery Unnesting

*****************************

SU: Unnesting query blocks in query block SEL$1 (#1) that are valid to unnest.

Subquery Unnesting on query block SEL$1 (#1)SU: Performing unnesting that does not?

require costing.

SU: Considering subquery unnest on query block SEL$1 (#1).

SU: Checking validity of unnesting subquery SEL$2 (#2)

SU: Passed validity checks.

SU: Transforming EXISTS subquery to a join.

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT "DEPT"."DEPARTMENT_NAME" "DEPARTMENT_NAME" FROM "HR"."EMPLOYEES"?

"EMP","HR"."DEPARTMENTS" "DEPT" WHERE "EMP"."DEPARTMENT_ID"="DEPT"."DEPARTMENT_ID"

******最终都发生SubQuery Unnest，被转换成相同的语句。

对于半连接，如果要适用exsits的话子查询和外查询是要有相关性的。

看看非半连接的情况：

HR@lipei> set autotrace on

HR@lipei> select department_name

2 from hr.departments dept

3 where department_id in (select/*+ no_semijoin */ department_id from hr.employees emp);

11 rows selected.

Elapsed: 00:00:00.04

Execution Plan

----------------------------------------------------------

Plan hash value: 274863846

----------------------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 106 | 1802 | 3 (34)| 00:00:01 |

| 1 | VIEW | | 106 | 1802 | 3 (34)| 00:00:01 |

| 2 | HASH UNIQUE | | 106 | 2438 | 3 (34)| 00:00:01 |

| 3 | NESTED LOOPS | | 106 | 2438 | 2 (0)| 00:00:01 |

| 4 | INDEX FULL SCAN | EMP_DEPARTMENT_IX | 107 | 321 | 1 (0)| 00:00:01 |

| 5 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 20 | 1 (0)| 00:00:01 |

|* 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |

----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

HR@lipei> select department_name

2 from hr.departments dept

3 where exists (select/*+ no_semijoin */ 1 from hr.employees emp

4 where emp.department_id = dept.department_id);

11 rows selected.

Elapsed: 00:00:00.05

Execution Plan

----------------------------------------------------------

Plan hash value: 440241596

----------------------------------------------------------------------------------------

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

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 16 | 17 (0)| 00:00:01 |

|* 1 | FILTER | | | | | |

| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 432 | 3 (0)| 00:00:01 |

|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 2 | 6 | 1 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ */ 0 FROM "HR"."EMPLOYEES" "EMP" WHERE

"EMP"."DEPARTMENT_ID"=:B1))

3 - access("EMP"."DEPARTMENT_ID"=:B1)

执行计划发生鸟根本的改变哈。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1052644/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1052644/