select sjbmdm, bmmc, t.*, code_enum.dmz as methodname
from code_enum,
acl_dept,
(select bmdm, dm, sum(num) num
from (select *
from (select u.bmdm, l.dm, count(1) num
from (select yhdm, sys_log.bmdm, dm
from sys_log, code_enum
where
sys_log.logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
and sys_log.logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
and sys_log.logmodule = '1'
and sys_log.methodname = code_enum.dm
and sys_log.RETURNFLAG ='0'
and lb = '1012') l,
acl_user u,
acl_dept d
where l.yhdm = u.yhdm
and u.bmdm = d.bmdm
group by u.bmdm, dm)
union all (select bmdm, dm, 0 as num
from acl_dept,
(select dm
from code_enum
where lb = '1012')))
group by bmdm, dm) t
where acl_dept.bmdm = t.bmdm
and t.dm = code_enum.dm
and code_enum.lb = '1012'
order by t.bmdm, code_enum.px请问如何优化以上sql
oracle 执行计划 刚接触不是很会所以 请教西该sql如何优化才能提升速度,现在 plsql里查询要24秒才能出结果,sys_log表的数据比较大有好几百万条,
from code_enum,
acl_dept,
(select bmdm, dm, sum(num) num
from (select *
from (select u.bmdm, l.dm, count(1) num
from (select yhdm, sys_log.bmdm, dm
from sys_log, code_enum
where
sys_log.logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
and sys_log.logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
and sys_log.logmodule = '1'
and sys_log.methodname = code_enum.dm
and sys_log.RETURNFLAG ='0'
and lb = '1012') l,
acl_user u,
acl_dept d
where l.yhdm = u.yhdm
and u.bmdm = d.bmdm
group by u.bmdm, dm)
union all (select bmdm, dm, 0 as num
from acl_dept,
(select dm
from code_enum
where lb = '1012')))
group by bmdm, dm) t
where acl_dept.bmdm = t.bmdm
and t.dm = code_enum.dm
and code_enum.lb = '1012'
order by t.bmdm, code_enum.px请问如何优化以上sql
oracle 执行计划 刚接触不是很会所以 请教西该sql如何优化才能提升速度,现在 plsql里查询要24秒才能出结果,sys_log表的数据比较大有好几百万条,
解决方案 »
- 一个字段能否关联多个表?
- 如何用命令行来创建包中的存储过程?
- 怎样一次输出一个记录变量中的所有内容?
- 难题:关于更新表内记录的clob字段,分不够再加,帮顶也给分
- 大哥吗,oracle9i中有一张表可以列出oracle所有的表,请问那张表叫什么名字,谢谢
- 问个触发器的问题
- 如何读取Oracle中的大字段,并且这个大字段的数据对应的是多个字段,如何保证按照这个顺序一一读写到这些字段中
- sql语言中有没有类似C语言中的switch case的语句??
- 在NLS_LANG=WE8ISO8859P1,数据库字符集为UTF16的环境下用Oralce OLEDB操作汉字的朋友吗?
- Oracle提取汉字去除标点符号
- Could not get the storage format of the medium
- 这两张表如何关联?
from code_enum,
acl_dept,
(select bmdm, dm, sum(num) num
from (select *
from (select u.bmdm, l.dm, count(1) num
from (select yhdm, sys_log.bmdm, dm
from sys_log, code_enum
where sys_log.methodname = code_enum.dm
and sys_log.logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
and sys_log.logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
and sys_log.logmodule = '1'
and sys_log.RETURNFLAG ='0'
and lb = '1012') l,
acl_user u,
acl_dept d
where l.yhdm = u.yhdm
and u.bmdm = d.bmdm
group by u.bmdm, dm)
union all (select bmdm, dm, 0 as num
from acl_dept,
(select dm
from code_enum
where lb = '1012')))
group by bmdm, dm) t
where acl_dept.bmdm = t.bmdm
and t.dm = code_enum.dm
and code_enum.lb = '1012'
order by t.bmdm, code_enum.px
FROM (SELECT bmdm, dm, sjbmdm, bmmc, dmz, px
FROM acl_dept,(select dm, dmz, px
FROM code_enum WHERE lb = '1012')) a,
(SELECT bmdm,methodname dm, count(*) num
FROM sys_log
WHERE logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
AND logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
AND logmodule = '1'
AND RETURNFLAG ='0'
GROUP BY bmdm,methodname) b
WHERE a.bmdm = b.bmdm(+)
AND a.dm = b.dm(+)
ORDER BY a.bmdm, a.px 2.假设sys_log.bmdm 与 acl_dept.bmdm 不同SELECT a.bmdm, a.dm, a.sjbmdm, a.bmmc, a.dmz, NVL(b.num,0)
FROM (SELECT bmdm, dm, sjbmdm, bmmc, dmz, px
FROM acl_dept,(select dm, dmz, px
FROM code_enum WHERE lb = '1012')) a,
(SELECT d.bmdm,methodname dm, count(*) num
FROM sys_log s,acl_user u,acl_dept d
WHERE s.yhdm = u.yhdm
AND u.bmdm = d.bmdm
AND logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
AND logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
AND logmodule = '1'
AND RETURNFLAG ='0'
GROUP BY d.bmdm,methodname) b
WHERE a.bmdm = b.bmdm(+)
AND a.dm = b.dm(+)
ORDER BY a.bmdm, a.px
更正:
SELECT a.bmdm, a.dm, a.sjbmdm, a.bmmc, a.dmz, NVL(b.num,0)
FROM (SELECT bmdm, dm, sjbmdm, bmmc, dmz, px
FROM acl_dept,(select dm, dmz, px
FROM code_enum WHERE lb = '1012')) a,
(SELECT u.bmdm,methodname dm, count(*) num
FROM sys_log s,acl_user u -- 并不需要连接acl_dept表
WHERE s.yhdm = u.yhdm
AND logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
AND logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
AND logmodule = '1'
AND RETURNFLAG ='0'
GROUP BY d.bmdm,methodname) b
WHERE a.bmdm = b.bmdm(+)
AND a.dm = b.dm(+)
ORDER BY a.bmdm, a.px
的
你写的SELECT a.bmdm, a.dm, a.sjbmdm, a.bmmc, a.dmz, NVL(b.num,0)
FROM (SELECT bmdm, dm, sjbmdm, bmmc, dmz, px
FROM acl_dept,(select dm, dmz, px
FROM code_enum WHERE lb = '1012')) a,
(SELECT bmdm,methodname dm, count(*) num
FROM sys_log
WHERE logtime>trunc(to_date('2012-06-01','YYYY-MM-DD'))
AND logtime<trunc(to_date('2012-06-30','YYYY-MM-DD'))+1
AND logmodule = '1'
AND RETURNFLAG ='0'
GROUP BY bmdm,methodname) b
WHERE a.bmdm = b.bmdm(+)
AND a.dm = b.dm(+)
ORDER BY a.bmdm, a.px
查询速度还是慢