谁可以帮我优化一下SQL(跪求)

Question

select appsystem_name,
       round(max(g.h1), 2),
       round(min(g.h2), 2),
       round(avg(g.h3), 2),
       round(max(g.h4), 2),
       round(min(g.h5), 2),
       round(avg(g.h6), 2)
  from (select appsystem_name,
               case
                 when pm.kpi_no = 100000041 then
                  valuemax
               end h1,
               case
                 when pm.kpi_no = 100000041 then
                  valuemin
               end h2,
               case
                 when pm.kpi_no = 100000041 then
                  valueavg
               end h3,
               case
                 when pm.kpi_no = 100000045 then
                  valuemax
               end h4,
               case
                 when pm.kpi_no = 100000045 then
                  valuemin
               end h5,
               case
                 when pm.kpi_no = 100000045 then
                  valueavg
               end h6
          from (select mv.appsystem_name,
                       
                       o.res_id,
                       
                       pm.valuemax,
                       
                       pm.valuemin,
                       
                       pm.valueavg,
                       
                       pm.kpi_no
                  from PM_day_P_ResHost pm,
                       
                       res_object o,
                       
                       res_dim_os os,
                       
                       res_node n,
                       
                       mv_relation mv
                union
                select mv.appsystem_name,
                       
                       o.res_id,
                       
                       pm.valuemax,
                       
                       pm.valuemin,
                       
                       pm.valueavg,
                       
                       pm.kpi_no
                  from PM_day_P_ResHost pm,
                       
                       res_object o,
                       
                       res_dim_os os,
                       
                       res_node n,
                       
                       mv_relation mv
                 where n.os_id = os.os_id
                   and o.res_id = n.res_id
                   and pm.kbp = o.res_id
                   and mv.relation_id = o.multiselect_id
                   and pm.kpi_no in (100000041, 100000045)
                   and pm.time_id >=20110401
                   and pm.time_id < 20110430
                   and pm.summarylevel = 2
                   and os.descr = 'Unix') pm) g
 group by g.appsystem_name

Answer 1

贴执行计划，可在pl/sql developer 中 f5，右键复制，
也可在sqlplus中
explain plan for '你的SQL';
select * from table(dbms_xplan.display);

Answer 2

SELECT appsystem_name,
       round(MAX(g.h1), 2),
       round(MIN(g.h2), 2),
       round(AVG(g.h3), 2),
       round(MAX(g.h4), 2),
       round(MIN(g.h5), 2),
       round(AVG(g.h6), 2)
  FROM (SELECT appsystem_name,
               CASE
                 WHEN pm.kpi_no = 100000041 THEN
                  valuemax
               END h1,
               CASE
                 WHEN pm.kpi_no = 100000041 THEN
                  valuemin
               END h2,
               CASE
                 WHEN pm.kpi_no = 100000041 THEN
                  valueavg
               END h3,
               CASE
                 WHEN pm.kpi_no = 100000045 THEN
                  valuemax
               END h4,
               CASE
                 WHEN pm.kpi_no = 100000045 THEN
                  valuemin
               END h5,
               CASE
                 WHEN pm.kpi_no = 100000045 THEN
                  valueavg
               END h6
          FROM (SELECT mv.appsystem_name,
                       o.res_id,
                       pm.valuemax,
                       pm.valuemin,
                       pm.valueavg,
                       pm.kpi_no
                  FROM PM_day_P_ResHost pm,
                       res_object       o,
                       res_dim_os       os,
                       res_node         n,
                       mv_relation      mv
                UNION
                SELECT mv.appsystem_name,
                       o.res_id,
                       pm.valuemax,
                       pm.valuemin,
                       pm.valueavg,
                       pm.kpi_no
                  FROM PM_day_P_ResHost pm,
                       res_object       o,
                       res_dim_os       os,
                       res_node         n,
                       mv_relation      mv
                 WHERE n.os_id = os.os_id
                   AND o.res_id = n.res_id
                   AND pm.kbp = o.res_id
                   AND mv.relation_id = o.multiselect_id
                   AND pm.kpi_no IN (100000041, 100000045)
                   AND pm.time_id >= 20110401
                   AND pm.time_id < 20110430
                   AND pm.summarylevel = 2
                   AND os.descr = 'Unix') pm) g
 GROUP BY g.appsystem_name上面将一个结果集的全部与这个结果集的部分union不是多余的吗？

Answer 3

select appsystem_name,
       round(max(g.h1), 2),
       round(min(g.h2), 2),
       round(avg(g.h3), 2),
       round(max(g.h4), 2),
       round(min(g.h5), 2),
       round(avg(g.h6), 2)
from (select appsystem_name,
             case when pm.kpi_no = 100000041 then valuemax end h1,
             case when pm.kpi_no = 100000041 then valuemin end h2,
             case when pm.kpi_no = 100000041 then valueavg end h3,
             case when pm.kpi_no = 100000045 then valuemax end h4,
             case when pm.kpi_no = 100000045 then valuemin end h5,
             case when pm.kpi_no = 100000045 then valueavg end h6
      from (select mv.appsystem_name,
                   o.res_id,
                   pm.valuemax,
                   pm.valuemin,
                   pm.valueavg,
                   pm.kpi_no
            from PM_day_P_ResHost pm,
                 res_object o,
                 res_dim_os os,
                 res_node n,
                 mv_relation mv
            where n.os_id = os.os_id
              and o.res_id = n.res_id
              and pm.kbp = o.res_id
              and mv.relation_id = o.multiselect_id
              and pm.kpi_no in (100000041, 100000045)
              and pm.time_id >=20110401
              and pm.time_id < 20110430
              and pm.summarylevel = 2
              and os.descr = 'Unix'
           ) pm
     ) g
group by g.appsystem_name

Answer 4

select appsystem_name,
       round(max(g.h1), 2),
       round(min(g.h2), 2),
       round(avg(g.h3), 2),
       round(max(g.h4), 2),
       round(min(g.h5), 2),
       round(avg(g.h6), 2)
from (select appsystem_name,
             case when pm.kpi_no = 100000041 then valuemax end h1,
             case when pm.kpi_no = 100000041 then valuemin end h2,
             case when pm.kpi_no = 100000041 then valueavg end h3,
             case when pm.kpi_no = 100000045 then valuemax end h4,
             case when pm.kpi_no = 100000045 then valuemin end h5,
             case when pm.kpi_no = 100000045 then valueavg end h6
      from (select mv.appsystem_name,
                   o.res_id,
                   pm.valuemax,
                   pm.valuemin,
                   pm.valueavg,
                   pm.kpi_no
            from PM_day_P_ResHost pm,
                 res_object o,
                 res_dim_os os,
                 res_node n,
                 mv_relation mv
            where n.os_id = os.os_id
              and o.res_id = n.res_id
              and pm.kbp = o.res_id
              and mv.relation_id = o.multiselect_id
              and pm.kpi_no in (100000041, 100000045)
              and pm.time_id >=20110401
              and pm.time_id < 20110430
              and pm.summarylevel = 2
              and os.descr = 'Unix'
           ) pm
     ) g
group by g.appsystem_name

Answer 5

我也留个记号，呵呵，顺便看看这个SQL