`
java-mans
  • 浏览: 11341028 次
文章分类
社区版块
存档分类
最新评论

Oracle union union all

 
阅读更多

转载自:

众所周知的几个结果集集合操作命令,今天详细地测试了一下,发现一些问题,记录备考。

假设我们有一个表Student,包括以下字段与数据:

drop table student;

create table student

(

id int primary key,

name nvarchar2(50) not null,

score number not null

);

insert into student values(1,'Aaron',78);

insert into student values(2,'Bill',76);

insert into student values(3,'Cindy',89);

insert into student values(4,'Damon',90);

insert into student values(5,'Ella',73);

insert into student values(6,'Frado',61);

insert into student values(7,'Gill',99);

insert into student values(8,'Hellen',56);

insert into student values(9,'Ivan',93);

insert into student values(10,'Jay',90);

commit;

Union和Union All的区别。

select *

from student

where id < 4

union

select *

from student

where id > 2 and id < 6

结果将是

1 Aaron 78

2 Bill 76

3 Cindy 89

4 Damon 90

5 Ella 73

如果换成Union All连接两个结果集,则返回结果是:

1 Aaron 78

2 Bill 76

3 Cindy 89

3 Cindy 89

4 Damon 90

5 Ella 73

可以看到,Union和Union All的区别之一在于对重复结果的处理

接下来我们将两个子查询的顺序调整一下,改为

--Union

select *

from student

where id > 2 and id < 6

union

select *

from student

where id < 4

看看执行结果是否和你期望的一致?

--Union All

select *

from student

where id > 2 and id < 6

union all

select *

from student

where id < 4

那么这个呢?

据此我们可知,区别之二在于对排序的处理。Union All将按照关联的次序组织数据,而Union将进行依据一定规则进行排序。那么这个规则是?我们换个查询方式看看:

select score,id,name

from student

where id > 2 and id < 6

union

select score,id,name

from student

where id < 4

结果如下:

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

和我们预料的一致:将会按照字段的顺序进行排序。之前我们的查询是基于id,name,score的字段顺序,那么结果集将按照id优先进行排序;而现在新的字段顺序也改变了查询结果的排序。并且,
是按照给定字段a,b,c...的顺序进行的order by。即结果是order by a,b,c...........的。我们看下一个查询:

select score,id,name

from student

where id > 2

union

select score,id,name

from student

where id < 4

结果如下:

56 8 Hellen

61 6 Frado

73 5 Ella

76 2 Bill

78 1 Aaron

89 3 Cindy

90 4 Damon

90 10 Jay

93 9 Ivan

99 7 Gill

可以看到,对于score相同的记录,将按照下一个字段id进行排序。如果我们想自行控制排序,是不是用order by指定就可以了呢?答案是肯定的,不过在写法上有需要注意的地方:

select score,id,name

from student

where id > 2 and id < 7

union

select score,id,name

from student

where id < 4

union

select score,id,name

from student

where id > 8

order by id desc

order by子句必须写在最后一个结果集里,并且其排序规则将改变操作后的排序结果。对于Union、Union All、Intersect、Minus都有效。

注意:

1,Union可以对字段名不同但数据类型相同的结果集进行合并;

2,如果字段名不同的结果集进行Union,那么对此字段的Order by子句将失效

=================================================================================================================

Intersect和Minus的操作和Union基本一致,这里一起总结一下:

Union,对两个结果集进行并集操作,不包括重复行,同时进行默认规则的排序;

Union All,对两个结果集进行并集操作,包括重复行,不进行排序;

Intersect,对两个结果集进行交集操作,不包括重复行,同时进行默认规则的排序;

Minus,对两个结果集进行差操作,不包括重复行,同时进行默认规则的排序。

可以在最后一个结果集中指定Order by子句改变排序方式

Oracle有很多值得学习的地方,这里我们主要介绍Oracle UNION ALL,包括介绍UNION等方面。通常情况下,用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意,以上规则只针对多个索引列有效。假如有column没有被索引,查询效率可能会因为您没有选择OR而降低。在下面的例子中,LOC_ID 和REGION上都建有索引。

高效:

SELECT LOC_ID 。 LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID ,
LOC_DESC ,REGION FROM LOCATION WHERE REGION = “MELBOURNE” 低效:

SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE” 用IN来替换OR:

这是一条简单易记的规则,但是实际的执行效果还须检验,在Oracle8i下,两者的执行路径似乎是相同的:

低效:

SELECT…. FROM LOCATION WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30 高效:

SELECT… FROM LOCATION WHERE LOC_IN IN (10,20,30); 避免在索引列上使用IS NULL和IS NOT NULL:

避免在索引中使用任何能够为空的列,Oracle将无法使用该索引。对于单列索引,假如列包含空值,索引中将不存在此记录。对于复合索引,假如每个列都为空,索引中同样不存在此记录。假如至少有一个列不为空,则记录存在于索引中。举例:假如唯一性索引建立在表的A列和B列上,并且表中存在一条记录的 A,B值为(123,null), Oracle将不接受下一条具备相同A,B值(123,null)的记录(插入)。然而假如任何的索引列都为空,Oracle将认为整个键值为空而空不等于空。因此您能够插入1000 条具备相同键值的记录,当然他们都是空! 因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引。

总是使用索引的第一个列:

假如索引是建立在多个列上,只有在他的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引。这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。

用Oracle UNION ALL替换UNION ( 假如有可能的话):

当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以Oracle UNION ALL的方式被合并,然后在输出最终结果前进行排序。假如用 Oracle UNION ALL替代UNION,这样排序就不是必要了。效率就会因此得到提高。需要注意的是,Oracle UNION ALL将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用Oracle UNION ALL的可行性。 UNION 将对结果集合排序,这个操作会使用到SORT_AREA_SIZE这块内存。对于这块内存的优化也是相当重要的。

1、前言
  目前所有使用Oracle作为数据库支撑平台的应用,大部分数据量比较庞大的系统,即表的数据量一般情况下都是在百万级以上的数据量。当然在Oracle 中创建分区是一种不错的选择,但是当你发现你的应用有多张表关联的时候,并且这些表大部分都是比较庞大,而你关联的时候发现其中的某一张或者某几张表关联之后得到的结果集非常小并且查询得到这个结果集的速度非常快,那么这个时候我考虑在Oracle中创建“临时表”。
  我对临时表的理解:在Oracle中创建一张表,这个表不用于其他的什么功能,主要用于自己的软件系统一些特有功能才用的,而当你用完之后表中的数据就没用了。Oracle的临时表创建之后基本不占用表空间,如果你没有指定临时表(包括临时表的索引)存放的表空的时候,你插入到临时表的数据是存放在 ORACLE系统的临时表空间中(TEMP)。

  2、临时表的创建

  创建Oracle临时表,可以有两种类型的临时表:会话级的临时表和事务级的临时表。
  1)会话级的临时表因为这这个临时表中的数据和你的当前会话有关系,当你当前SESSION不退出的情况下,临时表中的数据就还存在,而当你退出当前 SESSION的时候,临时表中的数据就全部没有了,当然这个时候你如果以另外一个SESSION登陆的时候是看不到另外一个SESSION中插入到临时表中的数据的。即两个不同的SESSION所插入的数据是互不相干的。当某一个SESSION退出之后临时表中的数据就被截断(truncate table,即数据清空)了。会话级的临时表创建方法:Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2……) On Commit Preserve Rows;举例create global temporary table Student(Stu_id Number(5),Class_id Number(5),Stu_Name Varchar2(8),Stu_Memo varchar2(200)) on Commit Preserve Rows ;
  2)事务级临时表是指该临时表与事务相关,当进行事务提交或者事务回滚的时候,临时表中的数据将自行被截断,其他的内容和会话级的临时表的一致(包括退出 SESSION的时候,事务级的临时表也会被自动截断)。事务级临时表的创建方法:Create Global Temporary Table Table_Name(Col1 Type1,Col2 Type2……) On Commit Delete Rows;举例:create global temporary table Classes(Class_id Number(5),Class_Name Varchar2(8),Class_Memo varchar2(200)) on Commit delete Rows ;
  3)、两种不通类型的临时表的区别:语法上,会话级临时表采用on commit preserve rows而事务级则采用on commit delete rows;用法上,会话级别只有当会话结束临时表中的数据才会被截断,而且事务级临时表则不管是commit、rollback或者是会话结束,临时表中的数据都将被截断。


  3、例子:

  1)、会话级(Session关闭掉之后数据就没有了,当Commit的时候则数据还在,当Rollback的时候则数据也是一样被回滚):

insert into student(stu_id,class_id,stu_name,stu_memo) values(1,1,''张三'',''福建''); insert into student(stu_id,class_id,stu_name,stu_memo) values(2,1,''刘德华'',''福州''); insert into student(stu_id,class_id,stu_name,stu_memo) values(3,2,''S.H.E'',''厦门''); SQL> select *from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- ------------------------
1 1 张三 福建
2 1 刘德华 福州
3 2 S.H.E 厦门
4 2 张惠妹 厦门


SQL> commit; Commit complete SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- --------------------
1 1 张三 福建
2 1 刘德华 福州
3 2 S.H.E 厦门
4 2 张惠妹 厦门


SQL>insert into student(stu_id,class_id,stu_name,stu_memo) values(4,2,''张惠妹'',''厦门''); 1 row inserted SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- ------------------
1 1 张三 福建
2 1 刘德华 福州
3 2 S.H.E 厦门
4 2 张惠妹 厦门
4 2 张惠妹 厦门


SQL> rollback ; Rollback complete SQL> select * from student ;

STU_ID CLASS_ID STU_NAME STU_MEMO
------ -------- -------- -------------------
1 1 张三 福建
2 1 刘德华 福州
3 2 S.H.E 厦门
4 2 张惠妹 厦门
SQL>


  2)、事务级(Commit之后就删除数据):本例子将采用以下的数据:

insert into classes(Class_id,Class_Name,Class_Memo) values(1,''计算机'',''9608'');
insert into classes(Class_id,Class_Name,Class_Memo) values(2,''经济信息'',''9602'');
insert into classes(Class_id,Class_Name,Class_Memo) values(3,''经济信息'',''9603'');


  在一个SESSION中(比如SQLPLUS登陆)插入上面3条记录,然后再以另外一个SESSION(用SQLPLUS再登陆一次)登陆,当你 select * from classes;的时候,classes表是空的,而你再第一次登陆的SQLPLUS中select的时候可以查询到,这个时候你没有进行commit或者rollback之前你可以对刚才插入的3条记录进行update、delete等操作,当你进行commit或者rollback的时候,这个时候由于你的表是事务级的临时表,那么在插入数据的session也看不到数据了,这个时候数据就已经被截断了。

  运行结果如下:

SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(1,''计算机'',''9608''); 1 row inserted SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(2,''经济信息'',''9602''); 1 row inserted SQL> insert into classes(Class_id,Class_Name,Class_Memo) values(3,''经济信息'',''9603''); 1 row inserted SQL> update classes set class_memo ='''' where class_id=3 ; 1 row updated SQL> select * from classes ;

CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- -------------------
1 计算机 9608
2 经济信息 9602
3 经济信息
SQL> delete from classes where class_id=3 ;
1 row deleted
SQL> select * from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- -----------
1 计算机 9608
2 经济信息 9602
SQL> commit;
Commit complete
SQL> select *from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- -------------
SQL>
再重复插入一次,然后rollback。
SQL> Rollback ;
Rollback complete
SQL> select * from classes ;
CLASS_ID CLASS_NAME CLASS_MEMO
-------- ---------- -----------
SQL>


  4、临时表的应用
  1)、当某一个SQL语句关联的表在2张及以上,并且和一些小表关联。可以采用将大表进行分拆并且得到比较小的结果集合存放在临时表中。
  2)、程序执行过程中可能需要存放一些临时的数据,这些数据在整个程序的会话过程中都需要用的等等。

  5、注意事项:
  1)、临时表的索引以及对表的修改、删除等和正常的表是一致的。
  2)、Oracle的临时表是Oracle8i才支持的功能特性,如果你的Oracle版本比较低的话,那么就可能没有办法用到了,如果你的Oracle 版本是8i的话,你还需要把$ORACLE_HOME/admin/${ORACLE_SID}/pfile目录下的init< ORACLE_SID>.ora初始参数配置文件的compatible修改为compatible = "8.1.0",我的服务器上就是这样子配置的。当然也可以修改为compatible = "8.1.6"
  以上是我在对大表进行优化的时候采用的一些手段,效果显著。


分享到:
评论

相关推荐

    oracle集合union、union all、intersect、minus

    oracle集合union、union all、intersect、minus

    union all与order by用法

    union all与order by用法,并详细举例,oracle pl/sql

    Oracle中的Union、Union_All、Intersect、Minus

    Oracle中的Union、Union_All、Intersect、Minus

    27.Oracle union多表查询1

    3、测试数据说明超女基本信息历史表(T_GIRL_HIS)中有4条记录,超女基本信息表(T_GIRL)中有3条记录,两个表中有相交的记录('0103'、'010

    Oracle中Union与Union All的区别(适用多个数据库)

    Union 与 Union ALL 的作用都是合并 SELECT 的查询结果集,那么它们有什么不同呢? Union 将查询到的结果集合并后进行重查,将其中相同的行去除。缺点:效率低; 而Union ALL 则只是合并查询的结果集,并不重新查询...

    Oracle 多行记录合并_连接_聚合字符串的几种方法_oracle_脚本之家1

    1.被集合字段范围小且固定型 灵活性 性能 难度 2.固定表固定字段函数法 灵活性 性能 难度 3.灵活表函数法 灵活性 性能 难度 4.一条SQL法 灵活性

    Oracle-[WITH & CONNECT

    union all select '1' as pid, '2' as id, '2' as name from dual union all select '1' as pid, '3' as id, '3' as name from dual union all select '2' as pid, '5' as id, '5' as name from dual union ...

    浅谈Oracle数据库性能的优化

    提出了一种优化Oracle 数据库的方法...Oracle 中SQL 语句的执行过程可分为解析(Parse)、执行(Execute)和提取结果(Fetch)三步,此方法就是通过对SQL 语句在Oracle 数据库中优化执行的三个过程来提高Oracle 数据库的性能。

    Oracle SQL最佳实践

    1.用EXISTS代替DISTINCT,消除sort operation  2.如果在GROUP BY中过滤数据,在WHERE从句中指定条件比在HAVING从句中有更好的...因此如果应用能够处理重复,或者确信没有重复记录,那么考虑使用UNION ALL代替UNION

    oracle学习日志总结

    尽量使用“&gt;=”,不要使用“&gt;”,用EXISTS代替IN(外表数据小情况),用大于或小于代替不等于,用右模糊查询(LIKE ‘…%’)代替模糊查询,用UNION ALL代替UNION,union代替or,trancate代替delete等. 7. count(1)比...

    oracle 数据库所有示例

    select iname,iage,ideptno from showinfo where ideptno in(10,20)union all select iname,iage,ideptno from showinfo where ideptno=10; --就好比 (20,30)减去30 只剩下20 了一样 结果A减去结果B select sname,...

    5分钟了解MySQL5.7中union all用法的黑科技

    union all在MySQL5.6下的表现 Part1:MySQL5.6.25 [root@HE1 ~]# MySQL -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: ...

    oracle sql of extracting table structure

    union all select distinct lower(all_c.table_name) table_name --,(case -- when column_id || to_char(column_id) -- when column_id (column_id) -- when column_id &gt; 100 then 'H' || substrb(to_char...

    oracle查询不含括号及不含指定字符的方法

    select 1 as id,'测试4321_CS' as name from dual union all select 2,'测试 1200(测试版)' from dual union all select 3,'测试123(测试版)' from dual union all select 4,'测试 1212(D2)' from dual ...

    spring springmvc mybatis easyui分页

    本项目为maven构建的一个SSM+easyUI的分页demo使用相对简单说明文档 http://blog.csdn.net/suijiarui/article/details/51360980

    Oracle的列转行问题

    Oracle中使用语句将行数据...网络一般推荐使用union all实现。这样的一个重要问题在于会造成对数据表的重复访问,性能是个重要问题。 基于此,作者设想了另外一种实现可能性。实践证明,这种方法有效提供了查询性能。

    oracle的sql优化

     在不需要考虑重复记录合并时候用Union All来代替Union  使用显性游标而不使用隐性游标,特别是大数据量情况下隐性游标对性能影响很大  是否使用函数的问题  用直接的表关联来代替Exist.用Exist或Not Exists来...

    Oracle Database 11g初学者指南--详细书签版

    4.11.2 union all 109 4.11.3 intersect 110 4.11.4 minus 110 4.12 视图 112 4.13 序列 113 4.14 约束:与实体模型的联系、类型、延迟 114 4.14.1 与实体模型的联系 115 4.14.2 类型 115 4.14.3 延迟 116 ...

Global site tag (gtag.js) - Google Analytics