【SQL 學習】minus 和intsect 的排序方式！
前面的文章說 minus 和intsect 的排序方式和union的排序方式是一樣的，這裡做一個實驗驗證一下：
SQL> select id ,name ,text from t
2 minus
3 select id ,name ,text from t1;
未選定行
執行計劃
----------------------------------------------------------
Plan hash value: 452211453
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 270 | 9 (67)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 9 | 81 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 9 | 81 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 21 | 189 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 21 | 189 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
387 bytes sent via SQL*Net to client
404 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> set linesize 120
SQL> select id ,name ,text from t1
2 minus
3 select id ,name ,text from t;
ID NAM TEX
---------- --- ---
2 a1 b1
2 a1 b2
2 a1 b3
2 a2 b2
3 a1 b1
3 a1 b2
3 a1 b3
3 a2 b1
3 a2 b2
3 a2 b3
已選擇10行。
執行計劃
----------------------------------------------------------
Plan hash value: 3599035886
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 270 | 9 (56)| 00:00:01 |
| 1 | MINUS | | | | | |
| 2 | SORT UNIQUE | | 21 | 189 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 21 | 189 | 4 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 9 | 81 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T | 9 | 81 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
統計資訊
---------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
642 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed
SQL> select id ,name ,text from t1
2 intersect
3 select id ,name ,text from t;
ID NAM TEX
---------- --- ---
1 a1 b1
1 a1 b2
1 a1 b3
1 a2 b1
1 a2 b2
1 a2 b3
2 a2 b1
2 a2 b3
已選擇8行。
執行計劃
---------------------------------------------------------
Plan hash value: 2269807545
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 270 | 9 (56)| 00:00:01 |
| 1 | INTERSECTION | | | | | |
| 2 | SORT UNIQUE | | 21 | 189 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T1 | 21 | 189 | 4 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 9 | 81 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T | 9 | 81 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed
SQL> select id ,name ,text from t
2 intersect
3 select id ,name ,text from t1;
ID NAM TEX
---------- --- ---
1 a1 b1
1 a1 b2
1 a1 b3
1 a2 b1
1 a2 b2
1 a2 b3
2 a2 b1
2 a2 b3
已選擇8行。
執行計劃
----------------------------------------------------------
Plan hash value: 3938425003
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 270 | 9 (67)| 00:00:01 |
| 1 | INTERSECTION | | | | | |
| 2 | SORT UNIQUE | | 9 | 81 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T | 9 | 81 | 3 (0)| 00:00:01 |
| 4 | SORT UNIQUE | | 21 | 189 | 5 (20)| 00:00:01 |
| 5 | TABLE ACCESS FULL| T1 | 21 | 189 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------
統計資訊
----------------------------------------------------------
1 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
620 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
8 rows processed
所有的結果都是全量排序！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-677739/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL 學習】INTERSECT,MINUS ,SQL
- 【SQL 學習】UNION 的排序方式！SQL排序
- 【SQL 學習】排序問題之order by與索引排序SQL排序索引
- 【學習】SQL基礎-003-過濾排序SQL排序
- 演算法學習之選擇排序和堆排序：演算法排序
- [Oracle] minus 和 not exists比較Oracle
- Spark SQL學習——DataFrame和DataSetSparkSQL
- 思考、學習新技術的原則和方式
- SQL SERVER和ORACLE的排序問題SQLServerOracle排序
- Oracle學習系列—關於字元數字混合排序和中文排序的問題Oracle字元排序
- SQL學習和練習的好地方（http://sqlzoo.cn）SQLHTTP
- Java集合和陣列的排序方式總結Java陣列排序
- Spark學習——排序ShuffleSpark排序
- 排序學習實踐排序
- 【AIX 學習】記憶體和CPU消耗程式排序AI記憶體排序
- mybatis原始碼學習------resultMap和sql片段的解析MyBatis原始碼SQL
- 透過sql trace比較常規 not in 、minus、not exists效率SQL
- 通過sql trace比較常規 not in 、minus、not exists效率SQL
- SQL 學習SQL
- sql學習SQL
- 【SQL】Oracle sql語句 minus函式執行效率與join對比SQLOracle函式
- 從排序原理到MYSQL中的排序方式排序MySql
- 學習筆記--- 比較排序之堆排序筆記排序
- 【SQL】SELECT語句中集合運算子 UNION/INTERSECT/MINUSSQL
- MySQL實現差集(Minus)和交集(Intersect)MySql
- 自我勸退式的學習方式
- Python 的 12 個學習方式Python
- 學習 Python 的 12 個方式Python
- 以遊戲化的方式學習Java遊戲Java
- sql_intersect交集_minus差集_並集union_union allSQL
- SQL學習-資料庫的備份和還原 分離和附加SQL資料庫
- 前端學習（2576）：選擇何種方式的路由和底層原理前端路由
- JAVA小練習氣泡排序，選擇排序和插入排序Java排序
- Spark SQL學習——UDF、UDAF和開窗函式SparkSQL函式
- Java學習的正確開啟方式Java
- 以互動的方式學習PythonPython
- Mysql學習總結（50）——Oracle，mysql和SQL Server的區別MySqlOracleServer
- 行業分析| 新的學習方式——線上自習室行業