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

Question

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

Answer 1

数据字典的设计很重要...------------------------------------------------------------------------------ 
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

Answer 2

完整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

Answer 3

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
红色的部分能够的去吗？

Answer 4

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