ORACLE sql 优化 运行速度太慢 要40秒 慢在 sum（）块

[ZSS_ZYK]

select
h.departid,
c.departname,
a.displayname,
f.title_id,
f.title_name,g.count_media,
count(decode(b.info_status,'3','已发布',null)) status_publish,

SUM(
decode(
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
,0,0,round(
(g.count_official /
(select count(*) from e_infodepart t, e_infomation d where t.content_id = b.content_id and t.content_id = d.content_id and d.info_status = 3)
),1
)
)
) as countA

from user_t a,e_infomation b ,edepart c,e_title_info e,e_infotitle f,e_target g,e_infodepart h

where h.content_id=b.content_id and e.title_id=f.title_id and c.departid =h.departid and
b.content_id=e.content_id and g.targetyear=f.title_id and
crtdate>=to_date(concat(to_char(trunc(sysdate,'mm'),'yyyy-MM-dd'),' 00:00:00'),'yyyy-MM-dd HH24:mi:ss') and
crtdate<=to_date(concat(to_char(last_day(sysdate),'yyyy-MM-dd'),' 23:59:59'),'yyyy-MM-dd HH24:mi:ss')

group by h.departid,c.departname,a.displayname,f.title_id ,f.title_name,g.count_official,g.count_media

[槑党--一缕风]

我写的不行？

[ZSS_ZYK]

恩 mar.count_official, 无效的标识符
注意一下 b表 和 d表 表名相同
t表 和 h表 表名相同

[1惯親手]

贴出表结构，和查询结果！

[槑党--一缕风]

select mar.* 
SUM(decode(status_publish,
                  0,
                  0,
                  round((g.count_official /
                       status_publish),
                        1))) as countA
from
 (select h.departid,
       c.departname,
       a.displayname,
       f.title_id,
       f.title_name,
       g.count_media,g.count_official,
       count(decode(b.info_status, '3', '已发布', null)) status_publish   
  from user_t       a,
       e_infomation b,
       edepart      c,
       e_title_info e,
       e_infotitle  f,
       e_target     g,
       e_infodepart h
 where h.content_id = b.content_id
   and e.title_id = f.title_id
   and c.departid = h.departid
   and b.content_id = e.content_id
   and g.targetyear = f.title_id
   and crtdate >= to_date(concat(to_char(trunc(sysdate, 'mm'), 'yyyy-MM-dd'),
                                 ' 00:00:00'),
                          'yyyy-MM-dd HH24:mi:ss')
   and crtdate <=
       to_date(concat(to_char(last_day(sysdate), 'yyyy-MM-dd'), ' 23:59:59'),
               'yyyy-MM-dd HH24:mi:ss')
) mar
 group by mar.departid,
          mar.departname,
          mar.displayname,
          mar.title_id,
          mar.title_name,
          mar.count_official,
          mar.count_media

再试试

[wqkjj]

这个主要取决于你的数据量、是否索引、临时表空间、相关表是否被独锁等等因素。。。。。

通常除非数据量小不影响效率，或者影响可以接受，否则不建议使用多表关联处理。

[槑党--一缕风]

在外面在套一层来求百分比。
你如果在里面，肯定是慢了，没查询一行 就要执行一次。
某列是单独查询数据库在得到结果的，如果数据少结果影响不大，如果数据多了，可想而知。
下面的sql 我没测试，应该是相对你而言要优化了不少

select mar.* 
SUM(decode(status_publish,
                  0,
                  0,
                  round((g.count_official /
                       status_publish),
                        1))) as countA
from
 (select h.departid,
       c.departname,
       a.displayname,
       f.title_id,
       f.title_name,
       g.count_media,
       count(decode(b.info_status, '3', '已发布', null)) status_publish   
  from user_t       a,
       e_infomation b,
       edepart      c,
       e_title_info e,
       e_infotitle  f,
       e_target     g,
       e_infodepart h
 where h.content_id = b.content_id
   and e.title_id = f.title_id
   and c.departid = h.departid
   and b.content_id = e.content_id
   and g.targetyear = f.title_id
   and crtdate >= to_date(concat(to_char(trunc(sysdate, 'mm'), 'yyyy-MM-dd'),
                                 ' 00:00:00'),
                          'yyyy-MM-dd HH24:mi:ss')
   and crtdate <=
       to_date(concat(to_char(last_day(sysdate), 'yyyy-MM-dd'), ' 23:59:59'),
               'yyyy-MM-dd HH24:mi:ss')
) mar
 group by mar.departid,
          mar.departname,
          mar.displayname,
          mar.title_id,
          mar.title_name,
          mar.count_official,
          mar.count_media

[ZSS_ZYK]

通过条件筛选后 还有两万多数据