“見識”很重要 Oracle的SQL調整–找出重複的行

周大師培訓完後馬上跑到另一個兄弟單位處解決問題，真是佩服周大師啊。

兄弟單位碰到了兩個棘手問題，一個資料imp時候報主鍵重複的錯誤，這個問題非常奇怪，暫時還不知道怎麼解決今天不說它啦。

另一個問題是出在這樣一條SQL上面：

select * from view_a awhere a.field_1,a.field_2,a.field_3,a.field_4in ( select field_1,field_2,field_3,field_4 from view_a group by field_1,field_2,field_3,field_4 having count(*) > 1)

這條語句的原意是按照field_1,field_2,field_3,field_4 找出重複的行，view_a有20354行資料，行寬不大，而且現時的資料是沒有重複行，也就是說in子句內的子查詢返回空集。

說完背景再說說遇到的問題吧。

該語句在舊資料庫(Oracle 9i , AIX)上面跑得非常快，但是在新資料庫（Oracle 10g , AS 5.2，資料是exp/imp過來的）上面跑了很久都出不來結果。

面對這個問題通常能作出以下兩個反應：

第一，該條語句的Cost很高，查看執行計畫，有幾步操作的確是非常低效；

第二，反應是該語句的執行計畫在10g和9i中的執行計畫不一樣；

就第一個反應來說，下一步就應該進入SQL調優的環節啦，不過這裡有個疑點，被公認將會出現效能問題的地方：

 select field_1,field_2,field_3,field_4 from view_a group by field_1,field_2,field_3,field_4 having count(*) > 1  

竟然非常快地返回了一個空集，難到是in這個操作符出問題了啦？使用in是非常簡潔的了，意思明了，很難找出一個更簡潔的操作符了呀。

接著進入第二條思路，難道說10g和9i的執行計畫不一樣，這個有可能，但是由於場地關係，一時半刻不能到9i的舊資料庫上面查看執行計畫。

按照這個思路走下去，如果改變執行計畫是不是會得到另一個結果呢？（更快、更慢）

怎麼改變執行計畫呢，第一種方案：Oracle中有個“提示”的功能，可以強制指定部分執行計畫，SQL Server 中也有這項功能；第二種方案：把SQL語句換一種寫法。

 

很久很久以前，在某個論壇中看到一篇文章，問為什麼MySQL為什麼不支援子查詢（那時候還沒是MySQL 4的年代），然後的回帖大概的意思都是說：MySQL還很年輕，明天會更好，云云。其中有一個牛人的回帖是：“任何子查詢都可以用串連(join)來代替”。按照這位牛人的思路，我把語句改寫成這樣：

select a.* from view_a ainner join ( select field_1,field_2,field_3,field_4 from table_a group by field_1,field_2,field_3,field_4 having count(*) > 1) bon  a.field_1=b.field_1and a.field_2=b.field_2and a.field_3=b.field_3and a.field_4=b.field_4

執行計畫的確變了，跑一下，結果很快出來了。 

 

寫過SQL的人幾乎都能明白join和in，但是又有幾個能有上述牛人的“見識”的。

【轉】http://www.cnblogs.com/killkill/archive/2009/04/13/1434825.html