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
看执行计划少了很多,实际执行的时候,时间减少不大。
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
看执行计划少了很多,实际执行的时候,时间减少不大。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24237320/viewspace-2134080/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24237320/viewspace-2134080/