full outer join 引起的问题？

Question

我的SQL:
　select  b.root_dept,sum(a5.n1),sum(a5.n2),sum(a5.n3) from 
　　(select a4.REQUESTED_DEPT dept,sum(a3.a1_num)n1,sum(a3.a2_num)n2,sum(a4.num)n3 from 
　　　　(select a2.dept dept,sum(a1.num) a1_num,sum(a2.num) a2_num from
　　　　　　(select count(*) num ,dept from probsummarym1 p where to_char(open_time,'YYYY-MM-DD') 　　　　　　　　　　　>= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09'　group by dept)
 　　　　　　　a1　full outer join 　　　　　(select count(*) num ,dept from incidentsm1 p where to_char(open_time,'YYYY-MM-DD') 　　　　　　　　　　　　>= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09'　group by dept)
 　　　　　　　a2 on a1.dept=a2.dept group by a2.dept) 
 　　　　　　　a3 full outer join
 　　　　　(select count(*) num ,REQUESTED_DEPT from ocmqm1 p where to_char(requested_date,'YYYY-MM-　　　　　　　　　DD') >= '2008-11-01' and to_char(requested_date,'YYYY-MM-DD')<= '2008-11-09'　group by 　　　　　　　　　　　REQUESTED_DEPT)
 　　　　　　　a4 on a3.dept=a4.REQUESTED_DEPT group by a4.REQUESTED_DEPT)a5,　　　　　　(select dept_name,level,lpad(' ',level*5)||eip_id eip_id,connect_by_isleaf　　isleaf,connect_by_root(eip_id) root,
connect_by_root(dept_name) root_dept,sys_connect_by_path(eip_id,'/') path  from deptm1  start with parent_oa_id = 1  
connect by prior eip_id = parent_oa_id  )
 b where a5.dept=b.dept_name(+) and b.root_dept is not null group by b.root,b.root_dept order by b.root_dept 我测试了以后发现查询很慢很慢，请各位高手们帮帮小弟弟，我刚刚学习oracle，但是在工作中就要使用，很急啊．
先谢谢了
 
 
 
 
  

Answer 1

select count(*) num ,dept from probsummarym1 p where to_char (open_time,'YYYY-MM-DD') >= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09' group by dept
select count(*) num ,dept from incidentsm1 i where to_char(open_time,'YYYY-MM-DD') >= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09' group by dept
select count(*) num ,dept from ocmqm1 o where to_char(requested_date,'YYYY-MM-DD') >= '2008-11-01' and to_char(requested_date,'YYYY-MM-DD')<='2008-11-09' group by dept  
其实我就想得到不含重复记录的全部部门，还有３个不同表统计的数量num
dept,p.num,i.num,p.num

Answer 2

select count(*) num ,dept from probsummarym1 p where to_char (open_time,'YYYY-MM-DD') >= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09' group by dept
select count(*) num ,dept from incidentsm1 i where to_char(open_time,'YYYY-MM-DD') >= '2008-11-01' and to_char(open_time,'YYYY-MM-DD')<= '2008-11-09' group by dept
select count(*) num ,dept from ocmqm1 o where to_char(requested_date,'YYYY-MM-DD') >= '2008-11-01' and to_char(requested_date,'YYYY-MM-DD')<='2008-11-09' group by dept  
其实我就想得到不含重复记录的全部部门，还有３个不同表统计的数量num
dept,p.num,i.num,p.num

Answer 3

排个板
那么多嵌套查询跟外连接还有分组阿，肯定慢阿
select b.root_dept, sum(a5.n1), sum(a5.n2), sum(a5.n3)
  from 　　 (select a4.REQUESTED_DEPT dept,
                  sum(a3.a1_num) n1,
                  sum(a3.a2_num) n2,
                  sum(a4.num) n3
             from 　　　　 (select a2.dept dept,
                               sum(a1.num) a1_num,
                               sum(a2.num) a2_num
                          from 　　　　　　 (select count(*) num, dept
                                         from probsummarym1 p
                                        where to_char(open_time, 'YYYY-MM-DD')
                                        　　　　　　　　　　　 >= '2008-11-01'
                                          and to_char(open_time, 'YYYY-MM-DD') <=
                                              '2008-11-09' 　group by dept) 　　　　　　　a1　full
                         outer join 　　　　　 (select count(*) num, dept
                                            from incidentsm1 p
                                           where to_char(open_time,
                                                         'YYYY-MM-DD')
                                           　　　　　　　　　　　　 >= '2008-11-01'
                                             and to_char(open_time,
                                                         'YYYY-MM-DD') <=
                                                 '2008-11-09' 　group by dept) 　　　　　　　a2 on a1.dept =
                                                                                           a2.dept
                         group by a2.dept) 　　　　　　　a3
             full outer join 　　　　　 (select count(*) num, REQUESTED_DEPT
                                     from ocmqm1 p
                                    where to_char(requested_date,
                                                  'YYYY-MM-　　　　　　　　　DD') >=
                                          '2008-11-01'
                                      and to_char(requested_date,
                                                  'YYYY-MM-DD') <=
                                          '2008-11-09' 　group by
                                    　　　　　　　　　　　REQUESTED_DEPT) 　　　　　　　a4 on a3.dept =
                                                                            a4.REQUESTED_DEPT
            group by a4.REQUESTED_DEPT)         a5,
       　　　　　　 (select dept_name,
                      level,
                      lpad(' ', level * 5) || eip_id eip_id,
                      connect_by_isleaf　　isleaf,
                      connect_by_root(eip_id) root,
                      connect_by_root(dept_name) root_dept,
                      sys_connect_by_path(eip_id, '/') path
                 from deptm1
                start with parent_oa_id = 1
               connect by prior eip_id = parent_oa_id) b
 where a5.dept = b.dept_name(+)
   and b.root_dept is not null
 group by b.root, b.root_dept
 order by b.root_dept

Answer 4

--给排了下，的确很眼晕！
--LZ能否不使用outer join,full join，而使用如a5.dept = b.dept_name(+)什么的。
select b.root_dept, sum(a5.n1), sum(a5.n2), sum(a5.n3)
  from (select a4.REQUESTED_DEPT dept,
               sum(a3.a1_num) n1,
               sum(a3.a2_num) n2,
               sum(a4.num) n3
          from 　　　　 (select a2.dept dept,
                                sum(a1.num) a1_num,
                                sum(a2.num) a2_num
                           from 　　　　　　 (select count(*) num, dept
                                                from probsummarym1 p
                                               where to_char(open_time,
                                                             'YYYY-MM-DD') >=
                                                     '2008-11-01'
                                                 and to_char(open_time,
                                                             'YYYY-MM-DD') <=
                                                     '2008-11-09'
                                               　group by dept) 　　　　　　　a1　full
                          outer join 　 (select count(*) num, dept
                                          from incidentsm1 p
                                         where to_char(open_time,
                                                       'YYYY-MM-DD') >=
                                               '2008-11-01'
                                           and to_char(open_time,
                                                       'YYYY-MM-DD') <=
                                               '2008-11-09'
                                         group by dept) 　　　　　　　a2 on a1.dept =
                                                                            a2.dept
                          group by a2.dept) 　　　　　　　a3
          full outer join 　　　　　 (select count(*) num, REQUESTED_DEPT
                                       from ocmqm1 p
                                      where to_char(requested_date,
                                                    'YYYY-MM-DD') >=
                                            '2008-11-01'
                                        and to_char(requested_date,
                                                    'YYYY-MM-DD') <=
                                            '2008-11-09' 　group by
                                      　REQUESTED_DEPT) 　　　　　　　a4 on a3.dept =
                                                                            a4.REQUESTED_DEPT
         group by a4.REQUESTED_DEPT) a5,
       (select dept_name,
               level,
               lpad(' ', level * 5) || eip_id eip_id,
               connect_by_isleaf　　isleaf,
               connect_by_root(eip_id) root,
               connect_by_root(dept_name) root_dept,
               sys_connect_by_path(eip_id, '/') path
          from deptm1
         start with parent_oa_id = 1
        connect by prior eip_id = parent_oa_id) b
 where a5.dept = b.dept_name(+)
   and b.root_dept is not null
 group by b.root, b.root_dept
 order by b.root_dept;
 
--有空时，再仔细分析一下。

Answer 5

--其他没有办法改，这是你的需求问题。把下面的改下并且相关字段建索引试试看：
WHERE TO_CHAR(OPEN_TIME,'YYYY-MM-DD') >= '2008-11-01'
  AND TO_CHAR(OPEN_TIME,'YYYY-MM-DD') <= '2008-11-09'
  
-> 
  
WHERE OPEN_TIME BETWEEN TO_DATE('2008-11-01','YYYY-MM-DD') AND TO_DATE('2008-11-09','YYYY-MM-DD')

Answer 6

--给排了下，的确很眼晕！
--LZ能否不使用outer join,full join，而使用如a5.dept = b.dept_name(+)什么的。select b.root_dept, sum(a5.n1), sum(a5.n2), sum(a5.n3)
  from (select a4.REQUESTED_DEPT dept,
               sum(a3.a1_num) n1,
               sum(a3.a2_num) n2,
               sum(a4.num) n3
          from 　　　　 (select a2.dept dept,
                                sum(a1.num) a1_num,
                                sum(a2.num) a2_num
                           from 　　　　　　 (select count(*) num, dept
                                                from probsummarym1 p
                                               where to_char(open_time,
                                                             'YYYY-MM-DD') >=
                                                     '2008-11-01'
                                                 and to_char(open_time,
                                                             'YYYY-MM-DD') <=
                                                     '2008-11-09'
                                               　group by dept) a1 　full
                          outer join 　 (select count(*) num, dept
                                          from incidentsm1 p
                                         where to_char(open_time,
                                                       'YYYY-MM-DD') >=
                                               '2008-11-01'
                                           and to_char(open_time,
                                                       'YYYY-MM-DD') <=
                                               '2008-11-09'
                                         group by dept) a2 on a1.dept =
                                                              a2.dept
                          group by a2.dept) a3
          full outer join (select count(*) num, REQUESTED_DEPT
                            from ocmqm1 p
                           where to_char(requested_date, 'YYYY-MM-DD') >=
                                 '2008-11-01'
                             and to_char(requested_date, 'YYYY-MM-DD') <=
                                 '2008-11-09' 　group by
                           　REQUESTED_DEPT) 　　　　　　　a4 on a3.dept =
                                                                 a4.REQUESTED_DEPT
         group by a4.REQUESTED_DEPT) a5,
       (select dept_name,
               level,
               lpad(' ', level * 5) || eip_id eip_id,
               connect_by_isleaf　　isleaf,
               connect_by_root(eip_id) root,
               connect_by_root(dept_name) root_dept,
               sys_connect_by_path(eip_id, '/') path
          from deptm1
         start with parent_oa_id = 1
        connect by prior eip_id = parent_oa_id) b
 where a5.dept = b.dept_name(+)
   and b.root_dept is not null
 group by b.root, b.root_dept
 order by b.root_dept;
 
--有空时，再仔细分析一下。

Answer 7

--看了一下：
--1.在probsummarym1,incidentsm1表中以dept分组进行了反复的count和sum;
--2.使用了目录树；
--3.outer join,full join，能否改为(+)进行连接？
--4.强列建议分段调试！如果每个分段测试正确的话，再联调。

Answer 8

1.full outer join  oracle 存在bug,需要到metalink下载补丁修正
2.oracle对于复杂的sql的执行计划很差,建议一是简化sql,二是改成存储过程,三是使用with as 子查询

Answer 9

oracle 用的外连接的话直接用（+）吧，看的也直观，真看晕了