一段sql优化问题，查询速度过慢，特别是数据量大的时候

Question

select count(1) count
  from (SELECT BPD.BPD_INSTANCE_ID AS INSTANCEID,
               a.reason,
               a.applytype,
               a.createtime suspendTime,
               BPD.TASK_ID,
               BPD.ACTIVITY_NAME,
               BPD.SUBJECT,
               BPD.STATUS,
               BPD.RCVD_DATETIME AS RECEIVEDDATE,
               BPD.SENT_DATETIME AS SENTDATE,
               BPD.READ_DATETIME AS READDATE,
               BPD.CLOSE_DATETIME AS CLOSEDATE,
               BPD.PRIORITY_ID AS PRIORITY,
               BASE.CREATETIME AS CREATEDATE,
               decode(P.PROCESSSTATE,
                      '暂停',
                      UF_ProjectDual(1),
                      BPD.PI_DUE_DATE) AS DUEDATE,
               BPD.DUE_DATE AS TASK_DUEDATE,
               BPD.USER_ID AS ASSIGNEDTOUSER,
               BPD.GROUP_ID AS ASSIGNEDTOROLE,
               P.ID AS PID,
               P.BUSINESSID AS BUSINESSID,
               BASE.PROJECTCODE AS PROJECTNO,
               BASE.PROJECTNAME AS PROJECTNAME,
               P.BUSINESSNAME,
               P.SUPERVISESTATE AS SUPERVISESTATE,
               '' AS REDLINENO,
               BASE.BUILDUNIT AS BUILDORG,
               BASE.BUILDADDRESS AS BUILDADDRESS,
               xm_model.DIST_TIMELIMITSTATE.UF_GETLIMITATIONSTATEDAY('FLOW',
                                                                     decode(BASE.CREATETIME,
                                                                            null,
                                                                            BPD.PI_CREATE_DATETIME,
                                                                            BASE.CREATETIME),
                                                                     decode(P.PROCESSSTATE,
                                                                            '暂停',
                                                                            UF_ProjectDual(1),
                                                                            BPD.PI_DUE_DATE)) AS PROCESSLIGHT,
               xm_model.DIST_TIMELIMITSTATE.UF_GETLIMITATIONSTATEDAY('SEGMENT',
                                                                     BPD.RCVD_DATETIME,
                                                                     decode(P.PROCESSSTATE,
                                                                            '暂停',
                                                                            UF_ProjectDual(1),
                                                                            BPD.DUE_DATE)) AS ACTIVITYLIGHT,
               BASE.FILECODE,
               P.PROCESSSTATE
          FROM xm_inst.TS_PROJECT P
          LEFT JOIN xm_inst.BZ_PROJECT BASE
            ON BASE.PID = P.ID
          LEFT JOIN xm_inst.TS_PROCESSINSTANCEINFO PTI
            ON PTI.PROJECTID = P.ID
          LEFT JOIN xm_model.TS_BUSINESS B
            ON P.BUSINESSID = B.ID
          left join xm_inst.ts_apply a
            on a.resourceid = P.id
         RIGHT JOIN (SELECT I.BPD_INSTANCE_ID,
                           I.INSTANCE_NAME,
                           B.BPD_ID,
                           T.TASK_ID,
                           T.SUBJECT,
                           T.ACTIVITY_NAME,
                           U.USER_NAME,
                           UGRP.GROUP_NAME,
                           T.RCVD_DATETIME,
                           T.CLOSE_DATETIME,
                           T.SENT_DATETIME,
                           T.READ_DATETIME,
                           T.STATUS,
                           T.PRIORITY_ID,
                           T.USER_ID,
                           T.GROUP_ID,
                           S.NAME            AS STATUS_NAME,
                           T.DUE_DATE,
                           T.DUE_TIME,
                           T.AT_RISK_DATE,
                           I.CREATE_DATETIME AS PI_CREATE_DATETIME,
                           I.DUE_DATE        AS PI_DUE_DATE
                      FROM CBPMDB.LSW_BPD_INSTANCE I
                     INNER JOIN CBPMDB. LSW_TASK T
                        ON I.BPD_INSTANCE_ID = T.BPD_INSTANCE_ID
                     INNER JOIN CBPMDB. LSW_BPD B
                        ON I.CACHED_BPD_VERSION_ID = B.VERSION_ID
                     INNER JOIN CBPMDB.LSW_TASK_STATUS_CODES S
                        ON T.STATUS = S.STATUS_VALUE
                      LEFT JOIN CBPMDB.LSW_USR_XREF U
                        ON T.USER_ID = U.USER_ID
                      LEFT JOIN CBPMDB.LSW_USR_GRP_XREF UGRP
                        ON T.GROUP_ID = UGRP.GROUP_ID
                     WHERE (S.NAME = 'Received' OR S.NAME = 'New')) BPD
            ON PTI.PROCESSINSTANCEID = BPD.BPD_INSTANCE_ID
           and BPD.TASK_ID = UF_GetMaxTaskId(BPD.BPD_INSTANCE_ID)
         WHERE 1 = 1
           AND P.PROCESSSTATE = '暂停'
           AND P.LIFESTATE = '正常'
           AND P.UNITID IN
               (select distinct t.id
                  from (select *
                          from xm_model.orup_organization a
                         start with a.id in (select b.organizationid
                                               from xm_model.orup_organization_user b
                                              where b.userid = 1)
                        connect by prior a.parentid = a.id
                               and prior a.orgtype = 2) t
                 where t.orgtype = 1))
这是项目中的一段sql，查询速度很慢，请问各位大神该怎么优化，提高查询速度

Answer 1

小弟对sql不了解，望大神帮忙啊

Answer 2

1.把你sql里面的嵌套查询全部改为临时表
2.在多表关联时使用/*+use_hash(a,b,c)*/

Answer 3

需要几个小时？
用UE正则表达式((?<=(?i)from\s)|(?<=(?i)join\s))\w+\.*\s*\w+查询出语句中涉及了13个表：
Xm_Inst.Ts_Project
Xm_Inst.Bz_Project
Xm_Inst.Ts_Processinstanceinfo
Xm_Model.Ts_Business
Xm_Inst.Ts_Apply
Cbpmdb.Lsw_Bpd_Instance
Cbpmdb. Lsw_Task
Cbpmdb. Lsw_Bpd
Cbpmdb.Lsw_Task_Status_Codes
Cbpmdb.Lsw_Usr_Xref
Cbpmdb.Lsw_Usr_Grp_Xref
Xm_Model.Orup_Organization
Xm_Model.Orup_Organization_User
1.里面相互之间有很多连接，你先确认一下这些表是不是视图，如果有视图，就把视图换成原始基本表试下，因为视图就相当于执行了二次查询。
2.把下面这段，放到前面去，先对P表（Xm_Inst.Ts_Project）进行过滤，再建立与其它表的连接。现在这段代码是先建立与其它表的连接，然后再过滤数据，我前面这样写的，跑了一天一夜都没有出结果。
         Where 1 = 1                                                                                          
           And P.Processstate = '暂停'                                                                        
           And P.Lifestate = '正常'                                                                           
           And P.Unitid In                                                                                    
               (Select Distinct T.Id                                                                          
                  From (Select *                                                                              
                          From Xm_Model.Orup_Organization A                                                   
                         Start With A.Id In (Select B.Organizationid                                          
                                               From Xm_Model.Orup_Organization_User B                         
                                              Where B.Userid = 1)                                             
                        Connect By Prior A.Parentid = A.Id                                                    
                               And Prior A.Orgtype = 2) T                                                     
                 Where T.Orgtype = 1))  
另外，把里面的IN换成EXISTS试试。还有Select *这种用法，数据量大了我觉得直接写上字段名好点，不知道对你这个的影响如何。
4.这句：Where (S.Name = 'received' Or S.Name = 'new')，也是先建立的连接，最后才来过滤数据。数据量小可以，大了不行。
5.使用并行执行：在SELECT后面加上/*+ parallel(8) */这样的东西，变成SELECT /*+ parallel(8) */……试试。

Answer 4

没有结构，没有执行计划，没有业务说明，很难光从sql上去做调优

Answer 5

这么多表做连接本身就是兵家大忌，业务表最多三个表进行连接操作，可以尝试把这句sql的业务拆分，把运算放到程序里进行。