oracle表交集并集,oracle交集,并集,差集

oracle 交集,15 NN varchar(10) 16); 17insertintotest2 values('test'oracle 交集,'A'); 18insertintotest2 values('test2','B'); 19insertintotest2 values('test2','C'); 20insertintotest2 values('test2','D'); 21insertintotest2 values('test2','E'); 22 231、交集：intersect 24[sql] 25SQL>select* fromtest1 intersectselect* fromtest2; 26 27NAME NN 28---------- ---------29test A 30 312、并集：union、unionall(注意两者的区?2[sql] 33SQL>select* fromtest1 unionselect* fromtest2; 34 35NAME NN 36---------- ---------37test A 38test1 B 39test1 C 40test1 D 41test1 E42test2 B 43test2 C 44test2 D 45test2 E 46 479 rowsselected。

48[sql] 49SQL>select* fromtest1 unionallselect* fromtest2; 50 51NAME NN 52---------- ---------53test A 54test1 B 55test1 C 56test1 D 57test1 E 58test A 59test2 B 60test2 C 61test2 D 62test2 E 63 6410 rowsselected。 65 663、差集：minus 67[sql] 68SQL>select* fromtest1 minus select* fromtest2; 69 70NAME NN 71---------- ---------72test1 B 73test1 C 74test1 D 75test1 E 76 77SQL>select* fromtest2 minus select* fromtest1; 78 79NAME NN 80---------- ---------81test2 B 82test2 C 83test2 D 84test2 E 8586最黑钳用 intersect 效率高呢还是 hash join 效率高呢? 87[sql] 88SQL>select* fromtest1 intersectselect* fromtest2; 89 90 91Execution Plan 92---------------------------------------------------------93Plan hash value: 4290880088 94 95--------------------------------------------------------------------96-------97| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 98Time | 99--------------------------------------------------------------------10-------0| 0 | SELECTSTATEMENT | | 5 | 140 | 8 (63)| 1000:00:01 | 1| 1 10| INTERSECTION | | | | | 2 | 10| 2 | SORT UNIQUE | | 5 | 70 | 4 (25)| 300:00:01 | 10| 3 | TABLEACCESS FULL| TEST1 | 5 | 70 | 3 (0)| 400:00:01 | 10| 4 | SORT UNIQUE | | 5 | 70 | 4 (25)| 500:00:01 | 10| 5 | TABLEACCESS FULL| TEST2 | 5 | 70 | 3 (0)| 600:00:01 | 10--------------------------------------------------------------------7-------10 8Note 10----9 - dynamicsampling used forthis statement (level=2) 11 0 11Statistics 1---------------------------------------------------------11 0 recursive calls 2 0 db block gets 11 14 consistent gets 3 0 physical reads 11 0 redo size 4 590 bytes sent via SQL*Net toclient11 523 bytes received via SQL*Net fromclient 5 2 SQL*Net roundtrips to/fromclient 11 2 sorts (memory) 6 0 sorts (disk) 11 1 rowsprocessed 7 11 8SQL>selecta。

* fromtest1 a,test2 b wherea。name=b。nameanda。nn=b。nn; 11 9 12Execution Plan 0---------------------------------------------------------12Plan hash value: 497311279 1 12--------------------------------------------------------------------2------12| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| 3Time | 12--------------------------------------------------------------------4------12| 0 | SELECTSTATEMENT | | 5 | 140 | 7 (15)| 500:00:01 | 12|* 1 | HASH JOIN | | 5 | 140 | 7 (15)| 600:00:01 | 12| 2 | TABLEACCESS FULL| TEST1 | 5 | 70 | 3 (0)| 700:00:01 | 12| 3 | TABLEACCESS FULL| TEST2 | 5 | 70 | 3 (0)| 800:00:01 | 12--------------------------------------------------------------------9------13 0Predicate Information (identified byoperation id): 13--------------------------------------------------1 13 1 - access("A"。

"NAME"="B"。"NAME"AND"A"。"NN"="B"。"NN") 2 13Note 3----13 - dynamicsampling used forthis statement (level=2) 4 13 5Statistics 13---------------------------------------------------------6 0 recursive calls13 7 13 8 13 9 14 0 14 1 14 2 14 3 14 4 14 5 14 6 14 7 14 8 14 9 15 0 15 1 15 2 15 3 15 4 15 5 15 6 15 7 15 80 15 0 0 590 523 2 0 0 1db block gets consistent gets physical reads redo size bytes sent via SQL*Net toclient bytes received via SQL*Net fromclient SQL*Net roundtrips to/fromclient sorts (memory) sorts (disk) rowsprocessed15 9 16 0 16 1 16 2 16 3 16 4

本文来自电脑杂谈，转载请注明本文网址：

http://www.pc-fly.com/a/shumachanpin/article-138674-1.html