關於oracle中的反連線
在之前的章節中見到討論過oracle中的半連線 http://blog.itpub.net/23718752/viewspace-1334483/
與半連線相對應的是反連線，簡而言之半連線就是查詢條件中的in,exists，反連線就是not in, not exists這種型別的連線。
在asktom中，tom也對大家關心的in,exists,not in, not exists的問題進行了大量的佐證和解釋。因為問題是在2001年左右提出來的，當時還是oracle 8的時代，帖子也沉裡許久，在2013年的時候，tom在自己的部落格中做了全新的解釋，說大家都在討論十幾年前的東西了。傳統RBO中的in,exists鮮明的對比在CBO中也都做了統一的最佳化處理，使得我們能夠更加專注於程式的邏輯實現。
|
|||||
首先建立兩個測試表，我們使用最熟悉的emp,dept表。為了保留原有的資料，我重新建立了兩個新的表，因為dept表中的資料太少，我就特意新增了一列資料使得效果更加明顯。
create table emp as select *from scott.emp;
create table dept as select *from scott.dept;
insert into dept values(50,'IT','BEIJING');
commit;
emp表中的資料情況如下，deptno目前只分布在3個部門。
SQL> select deptno from emp group by deptno;
DEPTNO
----------
30
20
10
dept表中有5個部們，那麼部門40,50就是emp中不存在的。也就是目前還沒有員工在deptno 40,50兩個部門。
SQL> select deptno,dname from dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
50 IT
我們來使用常用的反連線形式。查得deptno 為40,50的記錄。這也是我們預期的。
select *from dept where not exists(select null from emp where emp.deptno=dept.deptno);
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
40 OPERATIONS BOSTON
檢視執行計劃，能夠清晰的看到對應的反連線表示anti，當然我們也可以透過hint /*+hash_aj*/來指定為hash 反連線。
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 5 | 215 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 150 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
我們來看看和not exists等價的not in 形式。注意我在子查詢中新增了deptno is not null,如果沒有這個條件，not in和not exists是不等價的。如果emp中存在deptno為空的記錄，那麼整個查詢就會返回0行。
SQL> select *from dept where deptno not in (select deptno from emp where deptno is not null);
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
40 OPERATIONS BOSTON
執行計劃如下。
Execution Plan
----------------------------------------------------------
Plan hash value: 810774822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN ANTI SNA| | 5 | 215 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 150 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
下面的這種形式可能也是大家常犯的一個錯誤，就是會把原本的not in,not exists查詢改寫為下面的形式。結果就出乎意料了
select dept.* from dept,emp where dept.deptno!=emp.deptno;
。。。。
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
50 IT BEIJING
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
56 rows selected.
可以從執行計劃中看到，直接是對emp,dept做了nested loop join，這種錯誤需要避免。
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 2408 | 11 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 56 | 2408 | 11 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 150 | 4 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 11 | 143 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
-->not in ,not exists的等價實現
在實際的工作中，可能根據需要還會對not in ,not exists改寫為其他的等價形式。比如使用下面的形式。
SQL> select dept.* from dept ,emp where dept.deptno=emp.deptno(+) and emp.deptno is null;
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
40 OPERATIONS BOSTON
執行計劃如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 5 | 215 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 150 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
下面的這種形式化就對結果集進行了篩查。如果emp中的deptno為空，就設定一個不存在的deptno值。
select dept.* from dept where deptno not in (select nvl(deptno,'-1') from emp)
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT BEIJING
40 OPERATIONS BOSTON
執行計劃如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 810774822
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 215 | 8 (13)| 00:00:01 |
|* 1 | HASH JOIN ANTI SNA| | 5 | 215 | 8 (13)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 5 | 150 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
還可以使用集合來實現。不過這種方式使用的場景要少一些。這種方式直接把not in改換成了in的格式。
SQL> select *from dept where deptno in (select deptno from dept minus select deptno from emp);
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
50 IT BEIJING
執行計劃如下：
Execution Plan
----------------------------------------------------------
Plan hash value: 3106111345
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 150 | 13 (16)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DEPT | 5 | 150 | 4 (0)| 00:00:01 |
| 3 | MINUS | | | | | |
| 4 | SORT UNIQUE NOSORT| | 1 | 13 | 5 (20)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| DEPT | 1 | 13 | 4 (0)| 00:00:01 |
| 6 | SORT UNIQUE NOSORT| | 1 | 13 | 4 (25)| 00:00:01 |
|* 7 | TABLE ACCESS FULL| EMP | 1 | 13 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1380358/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於oracle中的半連線Oracle
- ORACLE 半連線與反連線Oracle
- oracle中的連線Oracle
- RMAN連線與oracle連線模式的關係Oracle模式
- 關於面向連線與面向無連線
- 關於Oracle 的url 連線 最後一個orcl的理解Oracle
- 關於mysql連線慢的分析.MySql
- 關於mysql連線的問題MySql
- 關於連線池的問題！
- oracle 連線數相關Oracle
- 【經典】連線oracle的總結（關於tnsname和監聽）Oracle
- 關於jetty連線關閉問題Jetty
- 備忘錄：關於.net程式連線Oracle資料庫Oracle資料庫
- 【JDBC】使用OracleDataSource建立連線池用於連線OracleJDBCOracle
- oracle最大連線數相關Oracle
- 關於oracle中的undoOracle
- Mysql關於長連線短連線優劣比較MySql
- 【Socket】關於socket長連線的心跳包
- Python--關於連線符+Python
- 關於資料庫連線池資料庫
- 關於canonmx538無線連線的問題
- oracle使用者連線相關Oracle
- Oracle 19c中連線RMAN客戶端的連線方法Oracle客戶端
- 關於oracle中的row pieceOracle
- Oracle的左連線和右連線Oracle
- MySQL反連線的優化總結MySql優化
- Oracle優化案例-用left join代替反連線 not in not exists（十）Oracle優化
- 關於 Homestead 連線 MySQL 問題MySql
- 關於外連線和where條件
- 關於java連線SQL資料庫JavaSQL資料庫
- 關於檢測TCP連線斷開TCP
- 關於在執行java連線MongoDB時遇到的連線超時問題JavaMongoDB
- 從原始碼中分析關於phpredis中的連線池可持有數目原始碼PHPRedis
- SpringBoot中關於 HikariPool、Druid及常用連線池的比較Spring BootUI
- Oracle中關於函式的使用Oracle函式
- oracle中關於null的定義OracleNull
- 關於xshell無法連線虛擬機器中linux虛擬機Linux
- Oracle(+)連線與Join連線Oracle