Oracle intersection, union, difference set

Oracle intersection, union, difference set

[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 int O test2 values ('test2', 'C'); insert into test2 values ('test2', 'D'); insert into test2 values ('test2 ', 'E'); 1. Intersection: intersect [SQL] SQL> select * from test1 intersect select * from test2; NAME NN ---------- -------- test A 2. Union: union and union all (note the difference) [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. difference set: 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 is used to calculate the intersection with intersect at last. Is it still highly efficient to use hash join? [SQL] SQL> select * from test1 intersect select * from test2; Execution Plan hash value: 4290880088 Bytes | 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 | notice Note ------dynamic sampling used for this statement (level = 2) statistics defaults 0 recursive cballs 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. * from test1 a, test2 B where. name = B. name and. nn = B. nn; Execution Plan -------------------------------------------------------- Plan hash value: 497311279 Bytes | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | minute | 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 | identified by operation id: ----------------------------------------------------------------- 1-access ("". "NAME" = "B ". "NAME" AND "". "NN" = "B ". "NN") Note ------dynamic sampling used for this statement (level = 2) statistics defaults 0 recursive cballs 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