oracle交集,并集,差集

[sql]
create table test1 
  name varchar (10), 
  NN varchar (10) 
); 
insert into test1 values ( 'test' , 'A' ); 
insert into test1 values ( 'test1' , 'B' ); 
insert into test1 values ( 'test1' , 'C' ); 
insert into test1 values ( 'test1' , 'D' ); 
insert into test1 values ( 'test1' , 'E' ); 
create table test2 
  name varchar (10), 
  NN varchar (10) 
); 
insert into test2 values ( 'test' , 'A' ); 
insert into test2 values ( 'test2' , 'B' ); 
insert into test2 values ( 'test2' , 'C' ); 
insert into test2 values ( 'test2' , 'D' ); 
insert into test2 values ( 'test2' , 'E' ); 
 
1、交集: intersect
[sql]
SQL> select * from test1 intersect select * from test2; 
   
NAME       NN 
---------- ---------- 
test       A 
 
2、并集: union union all (注意两者的区别)
[sql]
SQL> select * from test1 union select * from test2; 
   
NAME       NN 
---------- ---------- 
test       A 
test1      B 
test1      C 
test1      D 
test1      E 
test2      B 
test2      C 
test2      D 
test2      E 
   
9 rows selected. 
[sql]
SQL> select * from test1 union all select * from test2; 
   
NAME       NN 
---------- ---------- 
test       A 
test1      B 
test1      C 
test1      D 
test1      E 
test       A 
test2      B 
test2      C 
test2      D 
test2      E 
   
10 rows selected. 
 
3、差集:minus
[sql]
SQL> select * from test1 minus select * from test2; 
   
NAME       NN 
---------- ---------- 
test1      B 
test1      C 
test1      D 
test1      E 
   
SQL> select * from test2 minus select * from test1; 
   
NAME       NN 
---------- ---------- 
test2      B 
test2      C 
test2      D 
test2      E 
 
最后对于求交集用 intersect 效率高呢还是hash join 效率高呢?
[sql]
SQL> select * from test1 intersect select * from test2; 
   
   
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 4290880088 
   
----------------------------------------------------------------------------- 
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time    
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT    |       |     5 |   140 |     8  (63)| 00:00:01 | 
|   1 |  INTERSECTION       |       |       |       |            |          | 
|   2 |   SORT UNIQUE       |       |     5 |    70 |     4  (25)| 00:00:01 | 
|   3 |    TABLE ACCESS FULL | TEST1 |     5 |    70 |     3   (0)| 00:00:01 | 
|   4 |   SORT UNIQUE       |       |     5 |    70 |     4  (25)| 00:00:01 | 
|   5 |    TABLE ACCESS FULL | TEST2 |     5 |    70 |     3   (0)| 00:00:01 | 
----------------------------------------------------------------------------- 
   
Note 
----- 
    - dynamic sampling used for this statement ( level =2) 
   
   
Statistics 
---------------------------------------------------------- 
           0  recursive calls 
           0  db block gets 
          14  consistent gets 
           0  physical reads 
           0  redo size 
         590  bytes sent via SQL*Net to client 
         523  bytes received via SQL*Net from client 
           2  SQL*Net roundtrips to / from client 
           2  sorts (memory) 
           0  sorts (disk) 
           rows processed 
   
   
SQL> select a.* from test1 a,test2 b where a. name =b. name and a.nn=b.nn; 
   
   
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 497311279 
   
---------------------------------------------------------------------------- 
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time    
---------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT   |       |     5 |   140 |     7  (15)| 00:00:01 | 
|*  1 |  HASH JOIN         |       |     5 |   140 |     7  (15)| 00:00:01 | 
|   2 |   TABLE ACCESS FULL | TEST1 |     5 |    70 |     3   (0)| 00:00:01 | 
|   3 |   TABLE ACCESS FULL | TEST2 |     5 |    70 |     3   (0)| 00:00:01 | 
---------------------------------------------------------------------------- 
   
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   
    1 - access( "A" . "NAME" = "B" . "NAME" AND "A" . "NN" = "B" . "NN"
   
Note 
----- 
    - dynamic sampling used for this statement ( level =2) 
   
   
Statistics 
---------------------------------------------------------- 
           0  recursive calls 
           0  db block gets 
          15  consistent gets 
           0  physical reads 
           0  redo size 
         590  bytes sent via SQL*Net to client 
         523  bytes received via SQL*Net from client 
           2  SQL*Net roundtrips to / from client 
           0  sorts (memory) 
           0  sorts (disk) 
           rows processed
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页