本帖最后由 youthparabalic 于 2013-5-31 16:14 编辑
最近碰到了很多标量子查询,大家也都知道标量子查询的性能往往比较差
一、下面我把碰到的标量子查询分为以下几类,求大神帮忙改写,改写成有认为最优化的方式.在此也做个总结
1、最简单的标量自查询
2、子查询可能返回多行的限定子查询
3、子查询带有聚集函数
4、一个查询里面同时有好几个标量子查询,每个标量子查询的关联表,关联条件可能都不相同
5、(此后预留给我没有想到的)----------- 如果你碰到了什么心的标量子查询,请在回帖上标注,大家一起讨论
6、。。。。。。
二、下面我给出每个类别的例子
1、最简单的标量自查询
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1 = a.a1) from a
2、子查询可能返回多行的限定子查询
table :a(a1,a2),b(a1,b2)
select a2,(select b2 from b where b.a1 = a.a1 and rownum = 1) from a
3、子查询带有聚集函数
table :a(a1,a2),b(a1,b2)
select a2,(select sum(b2) from b where b.a1 = a.a1) from a
4、一个查询里面同时有好几个标量子查询,每个标量子查询的关联表,关联条件可能都不相同
table :a(a1,a2),b(a1,b2),c(a1,b2,c2)
select a2,(select c2 from b,c where b.a1 = a.a1 and b.b2=c.b2 and rownum = 1),(select b2 from b where b.a1 = a.a1 and rownum = 1), (select c2 from c where c.a1 = a.a1 and rownum = 1) from a
select a2,(select sum(c2) from b,c where b.a1 = a.a1 and b.b2=c.b2),(select sum(b2) from b where b.a1 = a.a1), (select sum(c2) from c where c.a1 = a.a1) from a
以上分别是四个分类的5条sql,请大家帮忙看看,第一次发帖,可能问题描述的不是很清楚,如果你有任何疑问,请在回帖上标明你的疑问 thank you
三:下面是改写方法
1、select a2,b2 from a,b where a.a1=b.a1(+);
2、select a2,bb.b2
from a,(select b2,a1,ROW_NUMBER() OVER (PARTITION BY b.a1 order by null) rn from b where b.rn = 1) bb
where a.a1 = bb.a1(+)
3、select a.a2,bb.sum_b2
from a,(select a1,sum(b2) sum_b2 from b group by a1) bb
where a.a1=bb.a1(+);
4.1、SELECT a.a1,bb.b2,cc.c2,cc1.c2
FROM a,(SELECT max(b2) b2,a1 FROM b GROUP BY a1) bb,
(SELECT max(c2) c2,a1 FROM c GROUP BY a1) cc,
(SELECT max(c2) c2,a1 FROM c WHERE EXISTS(SELECT 1 FROM b WHERE b.b2=c.b2) GROUP BY a1) cc1
WHERE a.a1=bb.a1(+) AND a.a1=cc.a1(+) AND a.a1=cc1.a1(+);
4.2、SELECT a.a1,bb.b2,cc.c2,cc1.c2
FROM a,(SELECT sum(b2) b2,a1 FROM b GROUP BY a1) bb,
(SELECT sum(c2) c2,a1 FROM c GROUP BY a1) cc,
(SELECT sum(c2) c2,a1 FROM c WHERE EXISTS(SELECT 1 FROM b WHERE b.b2=c.b2) GROUP BY a1) cc1
WHERE a.a1=bb.a1(+) AND a.a1=cc.a1(+) AND a.a1=cc1.a1(+);