Oracle有很多值得學習的地方,這裡我們主要介紹Oracle UNION ALL,包括介紹UNION等方面。通常情況下,用UNION替換WHERE子句中的OR将會起到較好的效果。對索引列使用OR将造成全表掃描。注意,以上規則隻針對多個索引列有效。假如有column沒有被索引,查詢效率可能會因為您沒有選擇OR而降低。在下面的例子中,LOC_ID 和REGION上都建有索引。
高效:
SELECT LOC_ID 。 LOC_DESC ,REGION FROM LOCATION WHERELOC_ID=10UNION SELECT LOC_ID ,
LOC_DESC ,REGION FROM LOCATION WHEREREGION= “MELBOURNE”
低效:
SELECT LOC_ID ,LOC_DESC ,REGION FROM LOCATION WHERELOC_ID=10ORREGION= “MELBOURNE”
用IN來替換OR:
這是一條簡單易記的規則,但是實際的執行效果還須檢驗,在Oracle8i下,兩者的執行路徑似乎是相同的:
低效:
SELECT…. FROM LOCATION WHERELOC_ID=10ORLOC_ID=20ORLOC_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這塊記憶體。對于這塊記憶體的優化也是相當重要的。
===================================================================================
Union和Union all用法
UNION指令的目的是将兩個SQL語句的結果合并起來。從這個角度來看, 我們會産生這樣的感覺,UNION跟JOIN似乎有些許類似,因為這兩個指令都可以由多個表格中撷取資料。 UNION的一個限制是兩個 SQL 語句所産生的欄位需要是同樣的資料種類。另外,當我們用 UNION這個指令時,我們隻會看到不同的資料值 (類似 SELECT DISTINCT)。 union隻是将兩個結果聯結起來一起顯示,并不是聯結兩個表
UNION 的文法如下: [SQL 語句 1]
UNION
[SQL 語句 2] 假設我們有以下的兩個表格,
Store_Information 表格 store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要找出來所有有營業額 (sales) 的日子。要達到這個目的,我們用以下的 SQL 語句:
SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales 結果:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
有一點值得注意的是,如果我們在任何一個 SQL 語句 (或是兩句都一起) 用 "SELECT DISTINCT Date" 的話,那我們會得到完全一樣的結果。
SQL Union All
UNION ALL 這個指令的目的也是要将兩個 SQL 語句的結果合并在一起。 UNION ALL 和 UNION 不同之處在于 UNION ALL 會将每一筆符合條件的資料都列出來,無論資料值有無重複。 UNION ALL 的文法如下: [SQL 語句 1]
UNION ALL
[SQL 語句 2] 我們用和上一頁同樣的例子來顯示出 UNION ALL 和 UNION 的不同。同樣假設我們有以下兩個表格:
Store_Information 表格 store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999
Internet Sales 表格 Date Sales
Jan-07-1999 $250
Jan-10-1999 $535
Jan-11-1999 $320
Jan-12-1999 $750
而我們要找出有店面營業額以及網絡營業額的日子。要達到這個目的,我們用以下的SQL語句:
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales 結果:
Date
Jan-05-1999
Jan-07-1999
Jan-08-1999
Jan-08-1999
Jan-07-1999
Jan-10-1999
Jan-11-1999
Jan-12-1999
============
表1
A B
a 1
b 0
c 3
d 0
e 2
表2
A B
c 0
e 4
合并兩個表除去重複的資料(以表2的資料為主),我們将會得到以下的表:
A B
a 1
b 0
c 0
d 0
e 4
select A,B from 表1 where A not in(select A from 表2)
union
select A,B from 表2
值得注意的是,sql union all的執行效率要比sql union效率要高很多,這是因為,使用sqlunion需要進行排重,而sql union All是不需要排重的,這一點非常重要,因為對于一些單純地使用分表來提高效率的查詢,完全可以使用sql union All。
還有,如果使用了union,無論是sql union還是sql union all一定要記住對資料庫表加上索引!
--合并重複行 select * from A union select * from B --不合并重複行 select * from A union all select * from B 按某個字段排序 --合并重複行 select * from ( select * from A union select * from B) AS T order by 字段名 --不合并重複行 select * from ( select * from A union all select * from B) AS T order by 字段名 //sql server版 Select * From ( select top 2 id,adddate,title,url from bArticle where ClassId=1 order by adddate desc) A Union All Select * From ( select top 2 id,adddate,title,url from bArticle where ClassId=2 order by adddate desc) B Union All Select * From ( select top 2 id,adddate,title,url from bArticle where ClassId=3 order by adddate desc) C Union All Select * From ( select top 2 id,adddate,title,url from bArticle where ClassId=4 order by adddate desc) D //mysql版 Select * From ( select id,adddate,title,url from bArticle where ClassId=1 order by adddate desc limit 0,2) A Union All Select * From ( select id,adddate,title,url from bArticle where ClassId=2 order by adddate desc limit 0,2) B Union All Select * From ( select id,adddate,title,url from bArticle where ClassId=3 order by adddate desc limit 0,2) C Union All Select * From ( select id,adddate,title,url from bArticle where ClassId=4 order by adddate desc limit 0,2) D