一个sql查询20多秒，表最多才8000数据，求优化。

a8522816 2013-08-20 03:16:30

select a.*,b.*,to_char(a.launch_time,'yyyy-MM-dd hh24:mi:ss') l_time,e.ename,ed.dictname,d.pathname servicecatalogname,

      b.applicant,b.applicant_com,t.customerorganname,(select tmp.uname  from (select row_number() 

      over(partition by  m.instance_id order by m.task_log_id) gid,m.user_name uname,m.processinstid  from bpm_rtm_log_task m where

       m.process_id= 'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp 

               where  tmp.gid=2 and a.processinstid = tmp.processinstid)

        from bpm_rtm_process_instance a,

             bpm_biz_itsm_genericservice b,

             itsm_dict_servicecatalog d,

             itsm_dict_gs_emergency e,

               eos_dict_entry ed,

               (select t.customeruserid,

               t.customerid,

               t.customerusername,

               t.customeruserorgan,

               t.customeruserphone2,

               t.customerusermail,

               t.customerusermsn,

               t.customeruserqq,

               t.createname,

               t.createtime,

               t.lastupdatename,

               t.lastupdatetime,

               t.helpdeskapplyrole,

               t.headername,

               t.headerid,

               b.customerorganname

               

          from itsm_dict_customer_user t, itsm_dict_customer_organ b

         where t.customeruserorgan = b.customerorganid(+)) t

        

       where a.processinstid = b.processinstid_

         and ed.dicttypeid = 'BPM_CFG_INST_STATUS'

             and a.instance_status = ed.dictid(+)

             and b.servicecatalog = d.id(+)

         and b.urgency_degree = e.id(+)

         and  b.applicant_id = t.customeruserid(+)

            and  b.applicant_com_id = t.customerid(+)

    

         and b.data_type_ = 'new'

 

    

       order by a.launch_time desc

...全文

547 21 打赏收藏转发到动态举报

写回复

用AI写文章

21 条回复

切换为时间正序

请发表友善的回复…

发表回复

a8522816 2014-04-13

打赏
举报

忘记结贴了。的确是子查询的问题。还有SQL没优化的情境下使用物化视图也使查询降到了毫秒级。可喜可贺。

28号怪人 2013-09-13

打赏
举报

有子查询当然慢，把你的子查询的表关联出来，不要用子查询

居然有人说长得比我帅 2013-09-12

打赏
举报

SELECT A.*,
       B.*,
       TO_CHAR(A.LAUNCH_TIME, 'yyyy-MM-dd hh24:mi:ss') L_TIME,
       E.ENAME,
       ED.DICTNAME,
       D.PATHNAME SERVICECATALOGNAME,
       B.APPLICANT,
       B.APPLICANT_COM,
       T.CUSTOMERORGANNAME,
       F.UNAME
  FROM BPM_RTM_PROCESS_INSTANCE A,
       BPM_BIZ_ITSM_GENERICSERVICE B,
       ITSM_DICT_SERVICECATALOG D,
       ITSM_DICT_GS_EMERGENCY E,
       EOS_DICT_ENTRY ED,
       (SELECT TMP.UNAME,TMP.PROCESSINSTID
          FROM (SELECT ROW_NUMBER() OVER(PARTITION BY M.INSTANCE_ID ORDER BY M.TASK_LOG_ID) GID,
                       M.USER_NAME UNAME,
                       M.PROCESSINSTID
                  FROM BPM_RTM_LOG_TASK M
                 WHERE M.PROCESS_ID =
                       'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') TMP
         WHERE TMP.GID = 2) F,
       (SELECT T.CUSTOMERUSERID,
               T.CUSTOMERID,
               T.CUSTOMERUSERNAME,
               T.CUSTOMERUSERORGAN,
               T.CUSTOMERUSERPHONE2,
               T.CUSTOMERUSERMAIL,
               T.CUSTOMERUSERMSN,
               T.CUSTOMERUSERQQ,
               T.CREATENAME,
               T.CREATETIME,
               T.LASTUPDATENAME,
               T.LASTUPDATETIME,
               T.HELPDESKAPPLYROLE,
               T.HEADERNAME,
               T.HEADERID,
               B.CUSTOMERORGANNAME
          FROM ITSM_DICT_CUSTOMER_USER T, ITSM_DICT_CUSTOMER_ORGAN B
         WHERE T.CUSTOMERUSERORGAN = B.CUSTOMERORGANID(+)) T
 WHERE A.PROCESSINSTID = B.PROCESSINSTID_
   AND A.PROCESSINSTID = F.PROCESSINSTID
   AND ED.DICTTYPEID = 'BPM_CFG_INST_STATUS'
   AND A.INSTANCE_STATUS = ED.DICTID(+)
   AND B.SERVICECATALOG = D.ID(+)
   AND B.URGENCY_DEGREE = E.ID(+)
   AND B.APPLICANT_ID = T.CUSTOMERUSERID(+)
   AND B.APPLICANT_COM_ID = T.CUSTOMERID(+)
   AND B.DATA_TYPE_ = 'new'
 ORDER BY A.LAUNCH_TIME DESC

longlongta 2013-09-12

打赏
举报

引用 16 楼 a8522816 的回复:

[quote=引用 15 楼 longlongta 的回复:] 其实你这个查询慢的主要原因是在select * from table 的* 的位置又出现的子查询： (select tmp.uname from (select row_number() over(partition by m.instance_id order by m.task_log_id) gid, m.user_name uname, m.processinstid from bpm_rtm_log_task m where m.process_id = 'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp where tmp.gid = 2 and a.processinstid = tmp.processinstid) 这样效率是很低很低的，你可以试着先把这个子查询去掉，保证查询时间立马降到5s以内。个人经验：所有的子查询都要尽量写到from 后面，不要出现在* 的位置

大神，被你说中了，就是这个问题。我小菜。请问具体怎么弄。[/quote]就是将*位置的子查询挪到from 后面

wangpengpengwang 2013-09-11

打赏
举报

16楼牛X啊嘎嘎嘎嘎

a8522816 2013-09-11

打赏
举报

引用 15 楼 longlongta 的回复:

其实你这个查询慢的主要原因是在select * from table 的* 的位置又出现的子查询： (select tmp.uname from (select row_number() over(partition by m.instance_id order by m.task_log_id) gid, m.user_name uname, m.processinstid from bpm_rtm_log_task m where m.process_id = 'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp where tmp.gid = 2 and a.processinstid = tmp.processinstid) 这样效率是很低很低的，你可以试着先把这个子查询去掉，保证查询时间立马降到5s以内。个人经验：所有的子查询都要尽量写到from 后面，不要出现在* 的位置

大神，被你说中了，就是这个问题。我小菜。请问具体怎么弄。

longlongta 2013-08-26

打赏
举报

其实你这个查询慢的主要原因是在select * from table 的* 的位置又出现的子查询： (select tmp.uname from (select row_number() over(partition by m.instance_id order by m.task_log_id) gid, m.user_name uname, m.processinstid from bpm_rtm_log_task m where m.process_id = 'cn.net.sinodata.sdbomc.itsm.workflow.genericservice.genericservice') tmp where tmp.gid = 2 and a.processinstid = tmp.processinstid) 这样效率是很低很低的，你可以试着先把这个子查询去掉，保证查询时间立马降到5s以内。个人经验：所有的子查询都要尽量写到from 后面，不要出现在* 的位置

babaerzi17 2013-08-21

打赏
举报

所以先过滤后连接的性能上是比后过滤要好。就你刚才说的5张表的太繁琐的话，建议你使用with b as 将过滤的表提出来然后再看就简洁多了。 with a as ( select * from AA where col='' ), b as ( select * from bb where col='' ) select a.*,b.* from a ,b where a.col=b.col

babaerzi17 2013-08-21

打赏
举报

关于这个问题。为什么要先过滤再连接。打个比方来说吧 A 表 1000条数据 B 表1000条数据。然后进行连接最后进行的是过滤。在oracle里面，最开始的A表与B表进行 A*B 为 1000*1000条数据进行匹配，需要10000000次。然后再进行过滤操作。如果我们先对A表进行过滤了，A表可能过滤后就剩下300条了，那连接进行的是 3000000次了。给个极端点的，过滤后只剩下1条，那我们需要进行连接查询也就仅仅1000次匹配。这个就是为什么需要先过滤再连接的。一般的话主表作为数据较少的一端性能比较好。

一直在进步 2013-08-20

打赏
举报

引用 4 楼 babaerzi17 的回复:

仅看SQL语句，需要优化的。首先sql语句本身可以优化，将 b.data_type_ = 'new' 提到 bpm_biz_itsm_genericservice表中先过滤一次，可以导致后面的左连接的查询次数减少。同理 ed.dicttypeid = 'BPM_CFG_INST_STATUS'这个过滤条件也可以先进行过滤，然后进行左连接其次在所有的左连接的连接条件上做索引。没有执行计划就只能看到这么多了。

这位大虾，我有几个疑问想请教下：疑问的内容也大致跟这帖子相似，是一个关于先连接再过滤、还是先过滤再连接的问题，孰优孰劣我至今都没弄明白，上次我写了个sql，根据我自己的理解采取的是先过滤的写法，并且不是用select*，而是直接过滤出整个查询想要的字段，由于4、5张表的连接，每个都先过滤字段和条件，导致整个sql非常长。针对这个问题还被我项目经理及客户批了，他们说我不会写sql，写的太复杂。先连接再过滤的写法更简洁，更清晰，说oracle会自动优化，我是非常的纳闷啊，但是又找不到合理的解释给他们。如果是先连接再过滤，sql长度将近可以缩短2/3,我也看了下执行计划，cost居然一样(也许我不会看

)。我真的很崩溃啊，大虾，能不能给点意见？？？？

babaerzi17 2013-08-20

打赏
举报

有问题加我QQ把，我也是练练手。。165292014.

a8522816 2013-08-20

打赏
举报

@babaerzi17 大神调整了下顺序。执行时间从21.544降到了21.138.我加索引试试

babaerzi17 2013-08-20

打赏
举报

就是上面的 from bpm_rtm_process_instance a, bpm_biz_itsm_genericservice b, itsm_dict_servicecatalog d, itsm_dict_gs_emergency e, eos_dict_entry ed, 这个地方的b 写成 from bpm_rtm_process_instance a, (select * from bpm_biz_itsm_genericservice where data_type_ = 'new' )b, itsm_dict_servicecatalog d, itsm_dict_gs_emergency e, (select * from eos_dict_entry where dicttypeid = 'BPM_CFG_INST_STATUS' )ed, 然后把最后的两个条件去掉 dicttypeid = 'BPM_CFG_INST_STATUS' data_type_ = 'new' 估计就问题不大了。

babaerzi17 2013-08-20

打赏
举报

看了你的执行计划之后。按照我上面说的两点进行优化应该没问题了。主要的都是进行的全表扫描。需要索引支持。就是在所有的左连接的字段上都建立索引，所有的左连接的表

a8522816 2013-08-20

打赏
举报

SELECT STATEMENT, GOAL = ALL_ROWS 375 1538 1056606 VIEW SDFRAME65 156 4212 252720 WINDOW SORT PUSHED RANK 156 4212 349596 TABLE ACCESS FULL SDFRAME65 BPM_RTM_LOG_TASK 71 4212 349596 SORT ORDER BY 375 1538 1056606 HASH JOIN RIGHT OUTER 148 1538 1056606 VIEW SDFRAME65 9 463 18057 HASH JOIN OUTER 9 463 18057 TABLE ACCESS FULL SDFRAME65 ITSM_DICT_CUSTOMER_USER 5 463 8334 TABLE ACCESS FULL SDFRAME65 ITSM_DICT_CUSTOMER_ORGAN 3 43 903 HASH JOIN RIGHT OUTER 139 1538 996624 TABLE ACCESS FULL SDFRAME65 ITSM_DICT_SERVICECATALOG 3 99 2871 HASH JOIN RIGHT OUTER 136 1538 952022 TABLE ACCESS FULL SDFRAME65 ITSM_DICT_GS_EMERGENCY 3 3 18 HASH JOIN 132 1538 942794 TABLE ACCESS BY INDEX ROWID SDFRAME65 EOS_DICT_ENTRY 3 11 352 INDEX RANGE SCAN SDFRAME65 EOS_DICT_ENTRYINDEX 2 11 HASH JOIN 129 1604 931924 TABLE ACCESS FULL SDFRAME65 BPM_RTM_PROCESS_INSTANCE 16 2267 394458 TABLE ACCESS FULL SDFRAME65 BPM_BIZ_ITSM_GENERICSERVICE 112 1604 652828

积木 2013-08-20

打赏
举报

引用 3 楼 a8522816 的回复:

果然是大神。我都不知道有这个东西。PLSQL的怎么看执行计划。哪些是您需要的。

好吧，话没说明白。看看你独立的SQL的执行计划。

babaerzi17 2013-08-20

打赏
举报

补充一下执行计划查看在oracle sqlplus set autotrace on ; 关闭执行计划 set autotrace off; plsql里面,点击一个命令窗口： sql> explain plan for ----sql语句 select * from test; sql> select * from table(dbms_xplain.display); 就可以查看到执行计划了。

babaerzi17 2013-08-20

打赏
举报

仅看SQL语句，需要优化的。首先sql语句本身可以优化，将 b.data_type_ = 'new' 提到 bpm_biz_itsm_genericservice表中先过滤一次，可以导致后面的左连接的查询次数减少。同理 ed.dicttypeid = 'BPM_CFG_INST_STATUS'这个过滤条件也可以先进行过滤，然后进行左连接其次在所有的左连接的连接条件上做索引。没有执行计划就只能看到这么多了。

a8522816 2013-08-20