466
SQL為王：oracle標量子查詢和表連接改寫
小魚（鄧秋爽）
雲和恩墨專家，有超過5年超大型數據庫專業服務經驗，擅長oracle 數據庫優化、SQL優化和troubleshooting
編輯手記：如何提高數據的查詢效率是每個人都關注的問題，今天讓我們來學習如何合理使用標量子查詢和表連接方式來提高查詢速度吧~
之前小魚就聽過了標量子查詢，不過對於其中的細節理解還是遠遠不夠，借助一部分資料和自己測試對標量子查詢做一點簡單的分析和介紹。
Oracle允許在select子句中包含單行子查詢，這個也就是oracle的標量子查詢，標量子查詢有點類似於外連接，當使用到外連接時我們可以靈活的將其轉化為標量子查詢。我們來看下麵的例子：
SQL> create table t1 as select * from all_users;
Table created.
SQL> create table t2 as select * from all_objects;
Table created.
SQL> select a.object_id,(select b.username from t1 b where a.owner=b.username) from t2 a;
49812 rows selected.
其執行計劃和統計信息如下：
標量子查詢其實還是一個子查詢，那麼它究竟是如何查詢的：首先走的是外部的查詢，比如上一個sql語句執行計劃，先全表掃描的T2 a，然後取T2 a表的每一行數據就去和T1 b去過濾，過濾條件是a.owner=b.username，如果符合則返回子查詢的值，如果不符合則用null補充。當然這個時候還有個類似的filter去重的運算，對於t2 a中重複的數據行不用再去和t1 b去過濾。
而上麵這個標量子查詢的sql語句其實是等價於下麵外連接sql語句的：
SQL> select a.object_id,b.username from t2 a,t1 b where a.owner=b.username(+) ;
49812 rows selected.
其執行計劃和統計信息如下
而如果標量子查詢中如果主查詢的一行對應子查詢返回有多個值，這個是不允許的，看下麵的例子
SQL> select a.username,b.object_id from t1 a,t2 b where a.username=b.owner(+);
29742 rows selected.
我們來看執行計劃和統計信息
看下麵的例子
SQL> select a.username,(select b.object_id from t2 b where a.username=b.owner) from t1 a;
select a.username,(select b.object_id from t2 b where a.username=b.owner) from t1 a
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
這裏由於a.username=b.owner，其中b.owner有多個相同的值，所以這裏返回的b.object_id可能有多個值，這裏就出現上述的ora-01427錯誤。
標量子查詢中也可以有聚合函數的出現：
SQL> select a.username,(select max(b.object_id) from t2 b where b.owner=a.username) from t1 a;
23 rows selected.
我們來看執行計劃和統計信息：
但是我們注意到上述標量子查詢卻存在一個問題，就是無法將子查詢展開為表連接，換句話說無法采用靈活的hash join outer的關聯方式。
關於標量子查詢和表關聯的性能簡介：
如果主查詢返回的數據較多，而子查詢中又沒有高效的索引，關聯列對應的主查詢表又沒有較多的重複值，那麼這個標量子查詢的執行成本是很大的,如上麵的標量子查詢和外連接的sql語句中可以看出外連接IO成本要明顯小於標量子查詢。
但是標量子查詢oracle內部確是有優化的，優化器cache了中間的結果,如果結果集不大，子查詢中又有高效的索引，那麼這個標量子查詢可能會比常規的表關聯更加高效。
小魚列出幾種常會涉及到的標量子查詢和表連接的sql改寫：
1. 最簡單的標量子查詢
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1=a.a1) from a
表連接：
select a2,b2 from a,b where a.a1=b.a1(+);
2. 標量子查詢帶有聚合函數
table :a(a1,a2),b(a1,b2)
select a2,(select sum(b2) from b where b.a1 = a.a1) from a
表連接1：
SELECT a2, x.sum_value
FROM a,
( SELECT SUM (b2) sum_value, a1
FROM b
GROUP BY a1) x
WHERE a.a1 = x.a1(+);
3. 包含行轉列的標量子查詢改寫
改寫的SQL如下：
4. 標量子查詢同時包含行轉列和聚合函數
改寫如下：
5. 標量子查詢中出現rownum=1或者rownum<2
原則上標量子查詢中出現rownum表示該SQL本來就是不嚴謹的，加上ROWNUM=1更多是為了防止標量子查詢中返回多行而出現錯誤：
比如上麵的SQL語句中對每個標量子查詢都添加了rownum=1的限製，那麼上述這個SQL語句如何改寫為表的外連接了。這裏首先取其中一個標量子查詢來做分析：
select data_object_id
from b
where b.object_id = a.object_id
and b.object_type = 'INDEX' and rownum=1
比如b表中有兩行數據都滿足b.object_id = a.object_id and b.object_type = 'INDEX'條件，rownum=1後oracle會根據b表的執行計劃取到第一條後就返回，這兩行數據都有可能取到，具體取哪一行要決定B表的訪問方式是索引掃描還是全表掃描等，而在這個SQL本意中無論取哪一條都是滿足業務需求的。那麼這個標量子查詢則可以簡化為：
select max(data_object_id)
from b
where b.object_id = a.object_id
and b.object_type = 'INDEX'
or
select min(data_object_id)
from b
where b.object_id = a.object_id
and b.object_type = 'INDEX'
那麼原SQL的標量子查詢可以簡化為：
如何改寫包含聚合函數的標量子查詢之前已經介紹過，這裏直接改寫為如下SQL語句：
其實這個b.object_type in ('INDEX', 'TABLE', 'JAVA', 'PROCEDURE', 'FUNCTION')寫不寫也都符合業務邏輯
6. 關於標量子查詢改寫後邏輯校驗：
select a.job,
a.deptno,
(select distinct dname from dept2 b where b.deptno=a.deptno) as dname
from emp a
有以下兩個改寫可供參考：
select distinct a.job,a.deptno,b.dname
from emp a
left join dept2 b on b.deptno=a.deptno;
select a.job,a.deptno,b.dname。
from emp a
left join (select dname,deptno from dept2 group by dname,deptno)b
on b.deptno=a.deptno
這裏xiaoyu覺得第二種寫法是完全複合業務邏輯的
簡要分析下，對於原標量子查詢中的(select distinct dname from dept2 b where b.deptno=a.deptno) as dname，由於標量子查詢中隻能返回單行，換句話說就是每個滿足b.deptno=a.deptno條件的數據隻能返回一行distinct dname，那麼就是select dname,deptno from dept2 group by dname,deptno生成的數據不會有(dname=x deptno=a)和(dname=y deptno=a)的數據,因為這類數據在原SQL語句中如果存在是會報錯的，那麼可以確定的是對於dept2表隻要deptno確定了，dname就確定了，所以這個改寫不會改變原SQL的含義。
7. 不等連接的標量子查詢改寫：
這個SQL主要是在標量子查詢中用了聚合函數min和非等值關聯trunc(contdate) >= a.opensaledate和trunc(buydate) >= a.opensaledate，聚合函數可以參考我們之前的寫法用group by先行構造，但是非等值連接如何改寫了。
由於有min聚合函數，必須要分組，但是上麵這個SQL如果對a.liceneid,a.data_source,a.street三個字段分組，則不滿足原SQL的含義，那麼這個SQL究竟如何改寫。
這裏參考oracle 查詢優化改寫案例書籍，要改寫這類不等連接的標量子查詢，可以參考下麵的寫法
這個改寫思路其實可以這麼理解：首先構造x表，這個表存儲了都是滿足了
ct.licenedid = a.licenceid、
and ct.data_source = a.data_source、
trunc(ct.contdate)>=a.opensaledate
trunc(ct.buydate)>=a.opensaledate條件的a.rowid、min(contdate)和min(buydate)數據，同樣有min函數如果需要將這些滿足條件的數據輸出需要再次關聯一次a表，而比較容易的就是將滿足條件的rowid去和a表重複做left join，滿足a.rowid=x.rid就全部輸出，不滿足在的就補全null
其實還可以用分析函數更簡單的改寫為
------The end
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:40