求助解决where后使用or查询慢的问题

[ldboy_zhuiming2008]

现写的SQL因为在where条件后的一个条件中用了两个or而且每种情况中都用了子查询，所以在有的数据库中查询比较慢，把or关系改成union模式，可以提高查询效率，但是因为灵活配置等要求，不能使用union，现请大家帮忙，寻求解决方法。先谢了。

[tzc]

代码贴出来看看

[dawugui]

既有or又有子查询,不慢才怪.

[Dave]

数据字典的设计很重要...



------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716

[yhuib]

给出具体的表结构和sql看看

[sxdtgsh]

把语句贴出来。。。。。

[ldboy_zhuiming2008]

完整SQL如下：
select /*+rule*/
mw_app.mwt_ud_lp_sheetindex.OBJ_ID,
SHEETID,
PATTERNNAME,
MACHINE,
DEPTNAME,
LEADER,
SIGNISSUE,
SHEETNAME,
PAGENUM,
STATUSNAME,
PERMITER,
PERMITSTARTTIME,
ENDTIME,
UPDATEINFO,
UNQUALIFIEDREASON,
SHEETTYPENAME
from mw_app.mwt_ud_lp_sheetindex,
mw_app.mwt_ud_lp_PATTERNINFO,
mw_app.mwt_ud_lp_STATUSMSG
where mw_app.mwt_ud_lp_sheetindex.PATTERNID =
mw_app.mwt_ud_lp_PATTERNINFO.PATTERNID
and mw_app.mwt_ud_lp_SHEETINDEX.SHEETSTATUSID =
mw_app.mwt_ud_lp_STATUSMSG.STATUSID
and mw_app.mwt_ud_lp_PATTERNINFO.moduletype = 1
and SHEETTYPE = 1
and ismainsheet = 1
and (deptid in
(select depid
from mw_app.mwv_ud_lp_department dep
start with depid in ('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior dep.DEPID = dep.DEPENDCODE) or
(mw_app.mwt_ud_lp_sheetindex.STATIONID in
(select distinct t2.stationid
from mw_app.MWT_UD_SBD_BDZ t1,
mw_app.mwt_ud_lp_wd_stationinfo t2
WHERE t1.obj_id = t2.obj_id
and t1.YXBM in (select depid
from mw_app.mwv_ud_lp_department
start with depid in
('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior depid = dependcode))) or
(mw_app.mwt_ud_lp_sheetindex.STATIONID in
(select distinct t2.stationid
from mw_app.MWT_UD_SBD_XLXX t1,
mw_app.mwt_ud_lp_wd_stationinfo t2
WHERE t1.obj_id = t2.obj_id
and t1.whbz in (select depid
from mw_app.mwv_ud_lp_department
start with depid in
('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior depid = dependcode))))
and sheettype <> 2
and sheettype <> 7
and ismainsheet = 1
and (ELECGRIDTYPE = 0 or ELECGRIDTYPE = 2)
order by to_number(substr(replace((case
when planstarttime is null then
'0'
else
planstarttime
end),
'-',
''),
0,
8)) desc

[ldboy_zhuiming2008]

你好，首先谢谢你的关注，SQL是可以正确执行的，就是在有的环境中执行很慢，但是有些环境却还可以不到1S（数据量少些750条记录）。红色的部分当然不能去掉，它代表了一种满足条件的情况，另外，你粘贴的SQL在红色部分后少了一个“(”所以不能不能执行。

[行舟]

1、由于对你的表结构、索引信息都不知道，这种SQL优化不好做啊！
2、建议将子查询的结构是否可以插入临时表。
3、对rule规则的使用：sql语句较长的情况下，可以省点SQL解析的时间，但是这样也会改变你的执行计划，不知道你这样做值不值啊？
4、我看见自查中还含有distinct 、in 、or 、<>、order by to_number(substr(replace((case when planstarttime is null then '0' else planstarttime end), '-', ''), 0, 8)) desc
5、最后我想问下：你的SQL能执行吗？
select /*+rule*/
mw_app.mwt_ud_lp_sheetindex.OBJ_ID,
SHEETID,
PATTERNNAME,
MACHINE,
DEPTNAME,
LEADER,
SIGNISSUE,
SHEETNAME,
PAGENUM,
STATUSNAME,
PERMITER,
PERMITSTARTTIME,
ENDTIME,
UPDATEINFO,
UNQUALIFIEDREASON,
SHEETTYPENAME
from mw_app.mwt_ud_lp_sheetindex,
mw_app.mwt_ud_lp_PATTERNINFO,
mw_app.mwt_ud_lp_STATUSMSG
where mw_app.mwt_ud_lp_sheetindex.PATTERNID =
mw_app.mwt_ud_lp_PATTERNINFO.PATTERNID
and mw_app.mwt_ud_lp_SHEETINDEX.SHEETSTATUSID =
mw_app.mwt_ud_lp_STATUSMSG.STATUSID
and mw_app.mwt_ud_lp_PATTERNINFO.moduletype = 1
and SHEETTYPE = 1
and ismainsheet = 1 and (deptid in (select depid
from mw_app.mwv_ud_lp_department dep
start with depid in
('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior dep.DEPID =
dep.DEPENDCODE) or (mw_app.mwt_ud_lp_sheetindex.STATIONID in
select distinct t2.stationid
from mw_app.MWT_UD_SBD_BDZ t1, mw_app.mwt_ud_lp_wd_stationinfo t2
WHERE t1.obj_id = t2.obj_id
and t1.YXBM in (select depid
from mw_app.mwv_ud_lp_department
start with depid in
('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior depid = dependcode))) or (mw_app.mwt_ud_lp_sheetindex.STATIONID in (select distinct t2.stationid
from mw_app.MWT_UD_SBD_XLXX t1,
mw_app.mwt_ud_lp_wd_stationinfo t2
WHERE t1.obj_id =
t2.obj_id
and t1.whbz in
(select depid
from mw_app.mwv_ud_lp_department
start with depid in
('63EBEC8E-E766-40D7-ACF4-FEA945102112-00168')
connect by prior
depid =
dependcode)))) and sheettype <> 2 and sheettype <> 7 and ismainsheet = 1 and (ELECGRIDTYPE = 0 or ELECGRIDTYPE = 2)
order by to_number(substr(replace((case
when planstarttime is null then
'0'
else
planstarttime
end),
'-',
''),
0,
8)) desc
红色的部分能够的去吗？

[ldboy_zhuiming2008]

自己解决了，呵呵。。通过创建视图，把or条件去掉，效率提高了几十倍，谢谢大家参与。