oracle sql优化帮帮忙

zty_ 发布于 2018/07/31 11:00
阅读 198
收藏 0

华为云11月刊推送:DIY微信问答机器人,高性能计算代码的20个技巧!>>>

查询速度超级慢  怎样可以优化一下

SELECT DISTINCT "view-grhttzInfo".*,
                VIEW_上传扫描件超期. TIME AS 上传扫描件超期,
                VIEW_归档超期. TIME AS 归档超期,
                NVL(BO_HT_VALIDATE.LJSQKPJE, 0) AS LJSQKPJE,
                NVL(BO_HT_VALIDATE.LJSQFKJE, 0) AS LJSQFKJE,
                NVL(BO_HT_VALIDATE.LJSQQRJE, 0) AS LJSQQRJE,
                NVL(BO_HT_VALIDATE.LJSQYFKJE, 0) AS LJSQYFKJE,
                NVL(BO_HT_VALIDATE.LJYFKHXJE, 0) AS LJYFKHXJE,
                VIEW_合同结算方式.VJSFS,
                VIEW_FIRST_UNCOMPLETED_HTGZJH.VMC,
                VIEW_种类.FPZL,
                VIEW_种类.SL,
                '资金计划' AS 资金计划
  FROM "view-grhttzInfo"
  LEFT JOIN VIEW_FIRST_UNCOMPLETED_HTGZJH
    ON "view-grhttzInfo".BINDID = VIEW_FIRST_UNCOMPLETED_HTGZJH.BINDID
  LEFT JOIN VIEW_种类税率
    ON "view-grhttzInfo".BINDID = VIEW_种类税率.BINDID
  LEFT JOIN VIEW_结算方式
    ON "view-grhttzInfo".BINDID = VIEW_合同结算方式.BINDID
  LEFT JOIN VIEW_结算方式
    ON "view-grhttzInfo".BINDID = VIEW_合同结算方式.BINDID
  LEFT JOIN BO_HT_VALIDATE
    ON "view-grhttzInfo".VHTBM = BO_HT_VALIDATE.HTBH
  LEFT JOIN VIEW_上传扫描件超期
    ON "view-grhttzInfo".BINDID = VIEW_上传扫描件超期.BIND_ID
  LEFT JOIN VIEW_归档超期
    ON "view-grhttzInfo".BINDID = VIEW_归档超期.BIND_ID
  LEFT JOIN VIEW_FIRST_UNCOMPLETED_HTGZJH
    ON "view-grhttzInfo".BINDID = VIEW_FIRST_UNCOMPLETED_HTGZJH.BINDID
 INNER JOIN (SELECT BIND_ID,
                    TARGET || '<' || ORGUSER.USERNAME || '>' AS TARGET
               FROM (SELECT BIND_ID, TARGET
                       FROM WF_TASK
                     UNION
                     SELECT BIND_ID, TARGET FROM WF_TASK_LOG) A
              INNER JOIN ORGUSER
                 ON A.TARGET = ORGUSER.USERID) T
    ON T.BIND_ID = "view-grhttzInfo".BINDID
 WHERE "view-grhttzInfo".
 ID IN (SELECT * FROM VIEW_HTID_ALL)
   AND ("view-grhttzInfo".VHTCBR = '@username ' OR
       "view-grhttzInfo".VHTZXR = '@username ' OR
       "view-grhttzInfo".VHTCKRY LIKE '%@username%' OR
       "view-grhttzInfo".BINDID IN
       (SELECT DISTINCT BIND_ID
           FROM (SELECT BIND_ID, TARGET, STATUS
                   FROM WF_TASK
                 UNION
                 SELECT BIND_ID, TARGET, STATUS FROM WF_TASK_LOG)
          WHERE STATUS = 1
            AND TARGET = '@uid '))

加载中
0
tinshen
tinshen

用一下sqldeveloper 有sql分析。

zty_
zty_
在哪分析
0
wad12302
wad12302

这sql 别优化了,重构业务吧,分成多个小sql 或者统计再输出

maradona
maradona
回复 @zty_ : 那别人更没办法了,懂业务才好优化
zty_
zty_
关键这是一个查询功能 设计这么多 我也没为办法
0
染墨若流云
染墨若流云

好复杂,完全看不懂啊

返回顶部
顶部