oracle 字符串加减?linux中的冒号什么用途?oracle交集,并集,差集

你是否正在寻找关于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

点击复制链接 与好友分享!回本站首页 上一篇：Oracle连接和半连接 下一篇：减少表扫描次数的优化 相关文章

Oracle-27-集合操作（交集、并集、差集

Oracle-27-集合操作（交集、并集、差集

文章 推荐

·ORACLE生成AWR报告 ·ORACLE常用性能监控SQL-更新中 ·oracle配置、安装、部署web应用 ·oracle学习笔记共享SQL减少硬解析 ·oracle学习笔记如何解决ORA-04031错误 ·oracle学习笔记如何设置sharedpool及s ·Oracle中索引的创建和使用 ·OracleDULDataUnloader数据恢复工具信 · · · · · · · ·

点击排行

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/shumachanpin/article-17388-1.html