“见识”很重要 记一起Oracle的SQL调整

周大师培训完后马上跑到另一个兄弟单位处解决问题，真是佩服周大师啊。

兄弟单位碰到了两个棘手问题，一个数据imp时候报主键重复的错误，这个问题非常奇怪，暂时还不知道怎么解决今天不说它啦。

另一个问题是出在这样一条SQL上面：

select * from view_a a
where 
a.field_1,a.field_2,a.field_3,a.field_4
in 
(
 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 a
inner 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
) b
on  a.field_1=b.field_1
and a.field_2=b.field_2
and a.field_3=b.field_3
and a.field_4=b.field_4

执行计划的确变了，跑一下，结果很快出来了。  :-)

 

写过SQL的人几乎都能明白join和in，但是又有几个能有上述牛人的“见识”的。