我們經常會對select結果進行對比，常用的函式如minus， 那麼當兩張表資料量較大時，有什麼方式可以提高效率呢？

#### minus 方式

minus 執行計劃如下所示， 透過排序後再進行對比
``` sql
select object_id from t2 minus select object_id from t1;
..
2255 rows selected.

Elapsed: 00:00:00.93

Execution Plan
----------------------------------------------------------
Plan hash value: 1578327006

-----------------------------------------------------------------------------------------
| Id  | Operation	       | Name	| Rows	| Bytes |TempSpc| Cost (%CPU)| Time	|
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |	|   102K|  2357K|	|  1318   (1)| 00:00:16 |
|   1 |  MINUS		       |	|	|	|	|	     |		|
|   2 |   SORT UNIQUE	       |	|   102K|  1304K|  2024K|   861   (1)| 00:00:11 |
|   3 |    TABLE ACCESS FULL   | T2	|   102K|  1304K|	|   371   (1)| 00:00:05 |
|   4 |   SORT UNIQUE	       |	| 82926 |  1052K|  1640K|   457   (1)| 00:00:06 |
|   5 |    INDEX FAST FULL SCAN| T1_IDX | 82926 |  1052K|	|    60   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  9  recursive calls   --遞迴
	  0  db block gets
       1681  consistent gets
       1798  physical reads
	  0  redo size
      41734  bytes sent via SQL*Net to client
       2173  bytes received via SQL*Net from client
	152  SQL*Net roundtrips to/from client
	  2  sorts (memory)
	  0  sorts (disk)
       2255  rows processed
```

#### 使用join方式替代

尤其資料量越大的情況下，join方式，不排序，走的hash演算法，執行效率會更好
``` sql
alter system flush buffer cache;
select t2.object_id t2_id from t1 right join t2 on t1.object_id=t2.object_id where t1.object_id is  null;
2255 rows selected.

Elapsed: 00:00:00.56

Execution Plan
----------------------------------------------------------
Plan hash value: 4276371593

----------------------------------------------------------------------------------------
| Id  | Operation	      | Name   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |   102K|  2609K|       |   653	 (1)| 00:00:08 |
|*  1 |  HASH JOIN RIGHT ANTI |        |   102K|  2609K|  2032K|   653	 (1)| 00:00:08 |
|   2 |   INDEX FAST FULL SCAN| T1_IDX | 82926 |  1052K|       |    60	 (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | T2     |   102K|  1304K|       |   371	 (1)| 00:00:05 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
	  0  recursive calls    --沒有遞迴呼叫
	  0  db block gets
       1692  consistent gets
       1534  physical reads
	  0  redo size
      41730  bytes sent via SQL*Net to client
       2173  bytes received via SQL*Net from client
	152  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
       2255  rows processed

```

上述語句可以使用右連線，也可以使用左連線

【SQL】Oracle sql語句 minus函式執行效率與join對比

相關文章