oracle交集,并集,差集

電腦雜談  發布時間:2020-01-28 19:04:08  來源:網絡整理

oracle 交集_oracle取交集_oracle 交集函數

oracle 交集,并集,差集?1[sql] 2createtabletest1 3( 4 namevarchar(10), 5 NN varchar(10) 6); 7insertintotest1 values('test','A'); 8insertintotest1 values('test1','B'); 9insertintotest1 values('test1','C'); 10insertintotest1 values('test1','D'); 11insertintotest1 values('test1','E'); 12createtabletest2 13( 14 namevarchar(10)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。

oracle 交集_oracle取交集_oracle 交集函數

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。

oracle 交集函數_oracle取交集_oracle 交集

* 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"。

oracle 交集函數_oracle 交集_oracle取交集

"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.gefhbj.live/a/shumachanpin/article-138674-1.html

    相關閱讀
    發表評論  請自覺遵守互聯網相關的政策法規,嚴禁發布、暴力、反動的言論

    • 沈弋凱
      沈弋凱

      就把他比做美國海軍和中國環南海演習

    • 閆賽賽
      閆賽賽

      春雨夢歸

    • 李夷簡
      李夷簡

      別學蘇聯搞冷戰

    熱點圖片
    拼命載入中...
    少林宝藏100在线客服 大庆52麻将看宝牌破解 武汉麻将卡五星怎么打 贵州弈乐最新版捉鸡麻将下载 山东群英会走势图最新 七乐彩开奖公告 东京热rq大乱交 福建22选5走势图1000期 最新手机赚钱软件 北京自动麻将机专卖店 甘肃11选5任选三推荐 江苏7位数 中彩网app下载 王中王资料精选大全 天津麻将怎么玩龙 老快3遗漏号36o一定牛 华东福彩15选5风彩走势图