Comparison of execution efficiency of minus function and join function in Oracle SQL statement

Time:2021-8-4

We often compare the select results. For common functions such as minus, what can be done to improve the efficiency when the two tables have a large amount of data?

Minus mode
The minus execution plan is shown as follows. Compare it after sorting
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 -- recursive
  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

Use join instead
Especially when the amount of data is large, the join method does not sort, and the hash algorithm is executedgameEfficiency will be better
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 -- no recursive game 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