FULL JOIN还是少用为妙

今天早上到公司，发现一个JOB跑了几个小时还没停下来，测试的时候这个任务执行两分钟就结束了。于是找DBA帮我查原因，原代码大致如此：

select g2.col1, g1.col2
  from (select nvl(tt.col1,pp.col1) col1, nvl(tt.col2,0) + nvl(pp.col2,0) col2
          from (select u.col1, count(*) as col2
                  from a g
                 inner join b u on g.username = u.username
                 where ...
                 group by u.col1) tt
                full join
                (select col1, count(*) as col2
                  from c t1, d t2
                 where ...
                 group by t2.col1) pp
         on tt.col1 = pp.col1) g1,
       b g2
 where ...;

 经过分解执行，发现没有问题，只要整体执行就特别慢。查看执行计划，发现问题出现在full join上， tt的结果比较多，pp的结果相当少。DBA建议改用unoin all，于是改为：

select g2.col1, g1.col2
  from (select col1, sum(col2)
          from (select u.col1, count(*) as col2
                  from a g
                 inner join b u on g.username = u.username
                 where ...
                 group by u.col1
                union all
                select col1, count(*) as col2
                  from c t1, d t2
                 where ...
                 group by t2.col1)
         group by username) g1,
       b g2
 where ...;

 重新执行任务，OK！

 

 

20110825

最近又遇到oracle的一个BUG,在存储过程中执行cube函数,产生600错误:

-- FOR <ORA-00600: 内部错误代码, 参数: [qctcte1], [0], [], [], [], [], [], []>
execute immediate 'alter session set "_optimizer_cost_based_transformation" = off';

 

同时,今天DBA帮我解决了一个问题,还是full join引起的,这个SQL的执行计划cost值大的可怕, 执行两个小时进度还只是百分之零点几.

DBA拿出了杀手锏:

alter session set "_complex_view_merging" = false;

问题搞定!(当然,实际上可以有别的办法绕过去,那就是不使用full join也能解决问题)