我们经常会对select结果进行对比，常用的函数如minus， 那么当两张表数据量较大时，有什么方式可以提高效率呢？

minus 方式

minus 执行计划如下所示， 通过排序后再进行对比

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算法，执行效率会更好

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

上述语句可以使用右连接，也可以使用左连接