[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
转载于:https://my.oschina.net/kkrgwbj/blog/330047