11g oracleexits 與in 之爭
SQL> create table emp as select * from scott.emp;
Table created.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> set timing on
SQL> set lines 20000
SQL> set autot on
SQL> select * from dept where deptno NOT IN ( select deptno from emp ) ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
22 consistent gets
10 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
2 - filter("DEPTNO" IS NOT NULL)
3 - filter("DEPTNO" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
2 - filter("DEPTNO" IS NOT NULL)
3 - filter("DEPTNO" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
結論：11g與空值有關，都可以用到anti的半連線演算法，執行計劃一樣，效能一樣
Table created.
SQL> create table dept as select * from scott.dept;
Table created.
SQL> set timing on
SQL> set lines 20000
SQL> set autot on
SQL> select * from dept where deptno NOT IN ( select deptno from emp ) ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
13 recursive calls
0 db block gets
22 consistent gets
10 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dept where not exists ( select deptno from emp where emp.deptno=dept.deptno) ;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
2 - filter("DEPTNO" IS NOT NULL)
3 - filter("DEPTNO" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
DEPTNO DNAME LOC
---------- -------------- -------------
40 OPERATIONS BOSTON
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 474461924
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 172 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI | | 4 | 172 | 6 (0)| 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 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
2 - filter("DEPTNO" IS NOT NULL)
3 - filter("DEPTNO" IS NOT NULL)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
674 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
結論：11g與空值有關，都可以用到anti的半連線演算法，執行計劃一樣，效能一樣
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30345407/viewspace-2150360/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 服務與資料之爭
- 單例與單例項之爭單例
- 蘋果與微信的打賞之爭 爭的就是收稅權蘋果
- Java 與 .NET 的平臺發展之爭Java
- 本地部署與雲管理的WLAN架構之爭架構
- Epic與蘋果之爭：悄悄變化的風向蘋果
- HTAP系統的問題與主義之爭
- 前端框架之爭丨除了Vue、Angular和React還有誰與之爭鋒前端框架VueAngularReact
- 為什麼說流媒體之爭也是新型廣告模式之爭？模式
- 介面的所有權之爭
- 騰訊與華為資料之爭談物聯網大資料發展之困大資料
- 破解圖靈測試之困，量化分析機器與人類智慧之爭圖靈
- REST與GraphQL的爭論REST
- TDSQL | 深度解讀HTAP系統的問題與主義之爭SQL
- Oracle 11g新特性之快取與連線池Oracle快取
- AI晶片之爭，你選誰？AI晶片
- 2019機器學習框架之爭：與Tensorflow競爭白熱化，進擊的PyTorch贏在哪裡？機器學習框架PyTorch
- 軟體測試江湖之公會武器之爭
- TensorFlow與PyTorch之爭，哪個框架最適合深度學習PyTorch框架深度學習
- 資料結構與演算法之美-王爭-極客時間資料結構演算法
- SQL標準之爭：甲骨文與PostgreSQL背後的江湖恩怨 - holisticsSQL
- Docker、Kubernetes、ApacheMesos之爭|一個與傳說不同的故事DockerApache
- 深入解析：由SQL解析失敗看開發與DBA的效能之爭SQL
- 未來不是Web與App的生死之爭，而是Web和App的融合WebAPP
- Daniel Steckly：休閒遊戲與硬核遊戲之間並非零和競爭遊戲
- 資訊化決定權之爭：權力之爭導致專案資源內耗（轉）
- 如何看待Serverless資料庫之爭?Server資料庫
- 是否要做Code Review？與BAT資深架構師爭論之後的思考ViewBAT架構
- 大資料爭論：批處理與流處理的C位之戰大資料
- 蘋果與FBI之爭：系統後的蟲洞，老大哥的眼睛蘋果
- 基於執行緒與基於事件的併發程式設計之爭執行緒事件程式設計
- 又一入口之爭：淺析智慧路由器的發展與趨勢路由器
- Oracle之11g DataGuardOracle
- 企業競爭情報蒐集與分析
- 谷歌與ChatGPT展開直接競爭谷歌ChatGPT
- 熱點塊競爭與解決
- 流量之爭即將上演，小程式將成為企業必爭之地！
- 架構之:微服務和單體服務之爭架構微服務