oracle中union和unionall區別與性能分析

[ 概要 ]

經常寫sql的同學可能會用到union和union all這兩個關鍵詞, 可能你知道使用它們可以將兩個查詢的結果集進行合並,

那麼二者有什麼區別呢? 下面我們就簡單的分析下.

[ 比較 ]

union: 對兩個結果集進行並集操作, 不包括重復行,相當於distinct, 同時進行默認規則的排序;

union all: 對兩個結果集進行並集操作, 包括重復行, 即所有的結果全部顯示, 不管是不是重復;

下面我們舉一個簡單的例子來證明上面的結論:

1. 准備數據:

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;

2. 比較不同點

查詢比較①

-- union all 
select * from student where id < 4
union all
select * from student where id > 2 and id < 6
   
-- union
select * from student where id < 4
union
select * from student where id > 2 and id < 6

union 查詢結果:

通過比較不難看出, union all不會去掉重復記錄, 而union去掉了重復的記錄.

查詢比較②

-- union all
select * from student where id > 2 and id < 6
union all
select * from student where id < 4

-- union
select * from student where id > 2 and id < 6
union
select * from student where id < 4

union 查詢結果:

通過比較不難看出, union all會按照關聯的次序組織數據, 而union會依據一定的規則進行排序.

那麼這個規則是什麼呢? 我們通過下面的查詢得出規律:

-- union
select score,id,name from student where id > 2 and id < 6
union
select score,id,name from student where id < 4

結論: 按照字段出現的順序進行排序, 之前的查詢相當於order by id, name, score, 剛剛的查詢相當於order by score, id, name.

[ 總結 ]

1. 因為union all僅僅是簡單的合並查詢結果, 並不會做去重操作, 也不會排序, 所以union all效率要比union高.

所以在能夠確定沒有重復記錄的情況下, 盡量使用union all.

2. 通常如果表有多個索引列時, 用union替換where子句中的or會起到較好的效果, 索引列使用or會造成全表掃描.

注意: 以上規則只針對多個索引列有效, 假如有column沒有被索引, 那還是用or吧.

例如: 還是使用上面的例子, 假定name和score上建有索引.

-- 高效
select id, name, score from student where name like '%y%' 
union
select id, name, score from student where score between 80 and 90

-- 低效
select id, name, score from student where name like '%y%' or score between 80 and 90