oracle 子查询修改,求讨论-标量子查询的改写办法

本帖最后由 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(+);