orale里的反连接

cty48663

于 2016-12-19 22:18:45 发布

阅读量98

点赞数

反连接:是指两个数据集之间的一种特殊连接类型，驱动数据集中最终返回的数据是丢弃匹配被驱动集记录后剩余的记录。

这里总结几点使用反连接的必要条件。
1、语句在NOT IN(!=ALL)或NOT EXISTS子句中有一个子查询。
2、使用NOT EXISTS语法时为了使得到的结果有意义，要使用相关的子查询。
3、NOT IN和NOT EXISTS字句不能包含于OR分支中。

注：这里的代码清单里所涉及的表来自HR Sample Schema。

代码清单1-1 使用NOT IN和NOT EXISTS

select department_name
from DEPARTMENTS
where DEPARTMENT_ID not in (select DEPARTMENT_ID from EMPLOYEES);
no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 4201340344
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 323 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN ANTI NA | | 17 | 323 | 6 (17)| 00:00:01 |
| 2 | SORT JOIN | | 27 | 432 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 432 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)| 00:00:01 |
|* 5 | SORT UNIQUE | | 107 | 321 | 4 (25)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")

SELECT department_name FROM departments
WHERE NOT EXISTS
(SELECT null FROM employees
WHERE departments.department_id = employees.department_id);
DEPARTMENT_NAME
------------------------------
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll
16 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3082375452
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 323 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS ANTI | | 17 | 323 | 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("DEPARTMENTS"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID")
==>上述代码中not in和not exists并没有返回相同值。这是因为not in是关心空值的，如果not in运算符返回了一个空值，
则整个查询不会返回任何记录。not exists则不关心空值。这里有2点需要说明下：
1、not in代码执行计划步骤2中出现了关键字‘ MERGE JOIN ANTI NA ’，ANTI表示使用了反连接，ANTI NA表示使用考虑空值的反连接。
11g以前，除非确定not in运算符不会返回空值，否则不能在not in查询中使用反连接。
2、为了使not in忽略空值的影响，可以有以下选择：

  查询列上使用nvl函数
  查询列上实现not null约束
   子查询中加上is not null谓语
   使用not exists代替not in

代码清单1-2 使用or分支禁用反连接

select department_name
from DEPARTMENTS
where 1=2 or DEPARTMENT_ID not in (select DEPARTMENT_ID from EMPLOYEES where DEPARTMENT_ID is not null);
DEPARTMENT_NAME
------------------------------
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll
16 rows selected.

SELECT department_name FROM departments
WHERE NOT EXISTS
(SELECT null FROM employees
WHERE 1=2 or departments.department_id = employees.department_id);
DEPARTMENT_NAME
------------------------------
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll
16 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 440241596
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 416 | 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( NOT EXISTS (SELECT 0 FROM "EMPLOYEES" "EMPLOYEES" WHERE
"EMPLOYEES"."DEPARTMENT_ID"=:B1))
3 - access("EMPLOYEES"."DEPARTMENT_ID"=:B1)
==>上述代码中在or字句中使用了not in和not exists，执行计划中都未使用反连接。

最后介绍下如何控制反连接执行计划
1、使用提示（hint)
ANTIJOIN-进行反连接

2、通过隐含参数
_always_anti_join 默认值为CHOOSE，允许优化器选择最高效的执行方法。将值设置为OFF则禁用了反连接，可在实例级别设定该参数。
_optimizer_null_aware_antijoin 默认值为TRUE，表示优化器会采用能够处理NULL值的反连接（即执行计划中出现‘ANTI NA'）。

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

转载于:http://blog.itpub.net/25923810/viewspace-2130979/

cty48663

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
orale里的反连接

反连接:是指两个数据集之间的一种特殊连接类型，驱动数据集中最终返回的数据是丢弃匹配被驱动集记录后剩余的记录。这里总结几点使用反连接的必要条件。 1、语句在NOT IN(!=ALL)或NOT EXI...
复制链接

扫一扫