qq_33880112 2017-08-25 02:01 采纳率: 0%
浏览 4320

SQL语句优化问题 执行速度太慢 ,如何优化

需要执行一段sql语句 但是效率 太低 能不能有办法 优化 子查询 太多了
SELECT ROWNUM RANKING, RES.*
FROM (SELECT STEPMEDAL.USER_ID,
SUM(STEPMEDAL.MEDALS) MEDALS,
REALNAME,
USER_IMG,
START_DATE,
END_DATE,
NVL(TOTAL.TOTAL_STEP_NUM, 0) TOTAL_STEP_NUM
FROM (SELECT INFO.USER_ID,
REALNAME,
START_DATE,
END_DATE,
USER_IMG,
NVL(MEDALS, 0) MEDALS
FROM (SELECT USERINFO.USER_ID,
EXT.REALNAME,
NVL(EXT.USER_IMG,0) USER_IMG,
TO_CHAR(ACTI.START_DATE, 'YYYY-MM-DD') START_DATE,
TO_CHAR(ACTI.END_DATE, 'YYYY-MM-DD') END_DATE
FROM JC_USER_EXT EXT,
JC_USER USERINFO,
JC_USER USERDEPART,
WALK_ACTIVITY_MAIN ACTI,
WALK_ACTIVITY_COMPANY_REL REL,
WALK_ACTIVITY_COMPANY_REL WACOM
WHERE USERDEPART.USER_ID = 12519
AND REL.COMPANY_ID = USERDEPART.DEPART_ID
AND WACOM.WA_ID = REL.WA_ID
AND USERINFO.DEPART_ID = WACOM.COMPANY_ID
AND EXT.USER_ID = USERINFO.USER_ID
AND ACTI.STATE = 1
AND ACTI.STATUS = 1
AND EXT.JOB_TYPE = 1
AND ACTI.WA_ID = WACOM.WA_ID) INFO
LEFT OUTER JOIN (SELECT USERINFO.USER_ID,
CASE
WHEN AVG(STEP.STEP_NUM) > 1800 THEN
1
ELSE
0
END MEDALS
FROM JC_USER USERINFO
LEFT OUTER JOIN WALK_USER_STEP STEP
ON USERINFO.USER_ID =
STEP.USER_ID, JC_USER
USERDEPART,
WALK_ACTIVITY_MAIN ACTI,
WALK_ACTIVITY_COMPANY_REL REL,
WALK_USER_STEP STEPDATE,
WALK_ACTIVITY_COMPANY_REL
WACOM
WHERE USERDEPART.USER_ID = 12519
AND REL.COMPANY_ID =
USERDEPART.DEPART_ID
AND WACOM.WA_ID = REL.WA_ID
AND USERINFO.DEPART_ID =
WACOM.COMPANY_ID
AND ACTI.STATE = 1
AND ACTI.STATUS = 1
AND ACTI.WA_ID = WACOM.WA_ID
AND STEP.STEP_ID =
STEPDATE.STEP_ID
AND TO_CHAR(STEPDATE.STEP_DATE,
'YYYY/MM') <>
TO_CHAR(SYSDATE, 'YYYY/MM')
AND STEPDATE.STEP_DATE BETWEEN
ACTI.START_DATE AND
ACTI.END_DATE
GROUP BY USERINFO.USER_ID,
TO_CHAR(STEPDATE.STEP_DATE,
'YYYY/MM')) STEPRES
ON INFO.USER_ID = STEPRES.USER_ID) STEPMEDAL
LEFT OUTER JOIN (SELECT STEPDATE.USER_ID,
SUM(STEPDATE.STEP_NUM) TOTAL_STEP_NUM
FROM JC_USER USERINFO
LEFT OUTER JOIN WALK_USER_STEP STEP
ON USERINFO.USER_ID = STEP.USER_ID,
JC_USER USERDEPART,
WALK_ACTIVITY_MAIN ACTI,
WALK_ACTIVITY_COMPANY_REL REL,
WALK_USER_STEP STEPDATE,
WALK_ACTIVITY_COMPANY_REL WACOM
WHERE USERDEPART.USER_ID = 12519
AND REL.COMPANY_ID =
USERDEPART.DEPART_ID
AND ACTI.WA_ID = WACOM.WA_ID
AND WACOM.WA_ID = REL.WA_ID
AND USERINFO.DEPART_ID =
WACOM.COMPANY_ID
AND ACTI.STATE = 1
AND ACTI.STATUS = 1
AND STEP.STEP_ID = STEPDATE.STEP_ID
AND STEPDATE.STEP_DATE BETWEEN
ACTI.START_DATE AND ACTI.END_DATE
GROUP BY STEPDATE.USER_ID) TOTAL
ON STEPMEDAL.USER_ID = TOTAL.USER_ID
GROUP BY STEPMEDAL.USER_ID,
REALNAME,
USER_IMG,
START_DATE,
END_DATE,
TOTAL.TOTAL_STEP_NUM
ORDER BY TOTAL_STEP_NUM DESC) RES

  • 写回答

6条回答 默认 最新

  • cloudyzhao 2017-08-25 02:03
    关注

    f5 查看下执行计划 看下性能瓶颈在哪 对应的优化

    评论

报告相同问题?

悬赏问题

  • ¥15 矩阵加法的规则是两个矩阵中对应位置的数的绝对值进行加和
  • ¥15 活动选择题。最多可以参加几个项目?
  • ¥15 飞机曲面部件如机翼,壁板等具体的孔位模型
  • ¥15 vs2019中数据导出问题
  • ¥20 云服务Linux系统TCP-MSS值修改?
  • ¥20 关于#单片机#的问题:项目:使用模拟iic与ov2640通讯环境:F407问题:读取的ID号总是0xff,自己调了调发现在读从机数据时,SDA线上并未有信号变化(语言-c语言)
  • ¥20 怎么在stm32门禁成品上增加查询记录功能
  • ¥15 Source insight编写代码后使用CCS5.2版本import之后,代码跳到注释行里面
  • ¥50 NT4.0系统 STOP:0X0000007B
  • ¥15 想问一下stata17中这段代码哪里有问题呀