前面的文章说 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
所有的结果都是全量排序！
