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) 

           1  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) 

           1  rows processed