[Oracle] minus 和 not exists比較

SQL> select count(*) from test;

  COUNT(*)
----------
  28835328

Elapsed: 00:00:09.48
SQL> select count(*) from test1;

  COUNT(*)
----------
  28833280

Elapsed: 00:00:09.20

SQL> select id from test minus select id from test1;

2048 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 145651196

-------------------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    28M|   329M|       |   424K (52)| 01:24:50 |
|   1 |  MINUS              |       |       |       |       |            |          |
|   2 |   SORT UNIQUE       |       |    28M|   164M|   331M|   208K  (2)| 00:41:40 |
|   3 |    TABLE ACCESS FULL| TEST  |    28M|   164M|       |   113K  (1)| 00:22:46 |
|   4 |   SORT UNIQUE       |       |    28M|   164M|   331M|   215K  (2)| 00:43:10 |
|   5 |    TABLE ACCESS FULL| TEST1 |    28M|   164M|       |   121K  (1)| 00:24:17 |
-------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        666  recursive calls
          5  db block gets
     858690  consistent gets
     942852  physical reads
          0  redo size
      40164  bytes sent via SQL*Net to client
       2020  bytes received via SQL*Net from client
        138  SQL*Net roundtrips to/from client
          0  sorts (memory)
          2  sorts (disk)
       2048  rows processed




SQL> select id from test where not exists (select id from test1 where test.id=test1.id)
  2  ;

2048 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 505731057

------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |   288K|  3379K|       |   181K  (1)| 00:36:19 |
|*  1 |  HASH JOIN RIGHT ANTI |          |   288K|  3379K|   494M|   181K  (1)| 00:36:19 |
|   2 |   INDEX FAST FULL SCAN| TEST1_PK |    28M|   164M|       | 18055   (2)| 00:03:37 |
|   3 |   TABLE ACCESS FULL   | TEST     |    28M|   164M|       |   113K  (1)| 00:22:46 |
------------------------------------------------------------------------------------------

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

   1 - access("TEST"."ID"="TEST1"."ID")


Statistics
----------------------------------------------------------
        724  recursive calls
          1  db block gets
     483516  consistent gets
     503636  physical reads
          0  redo size
      40164  bytes sent via SQL*Net to client
       2020  bytes received via SQL*Net from client
        138  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2048  rows processed

SQL> create table tt6 as select id from test minus select id from test1;

Table created.

Elapsed: 00:01:09.79
SQL> create table tt7 as select id from test where not exists (select id from test1 where test.id=test1.id);

Table created.

Elapsed: 00:01:02.35

看執行計劃少了很多，實際執行的時候，時間減少不大。