我们经常会对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
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
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