duckkeeper 2015-12-04 01:25 采纳率: 0%
浏览 2412

【求助】ORACLE中的SQL优化,涉及到用OR该怎么优化?

各位大神,小弟最近在执行以下sql时发现执行时间超长,跑完一次差不多要十小时,看执行计划就是卡在OR那里,现在想请教大神下面语句中的OR该做何种优化?补充说明:涉及到的各个字段已有索引,D表数据量在4200左右,T表全量在1000W左右,在子查询里做筛选后的TT数据量在300W左右。
已无分可用~跪求好人~

 SELECT
  D.ADDRESS_LEVEL_CODE
, COUNT(TT.UUID) AS BE_CHECK_WOMEN
FROM DIM_ADDRESS D
LEFT JOIN
(
SELECT T.UUID,T.HOUSEHOLD_LEVEL_CODE,T.RESIDENCE_LEVEL_CODE
FROM DIM_RESIDENT T
WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) >= 15
AND FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) <= 50
AND T.GENDER_CODE = '2' 
AND T.IS_ACTIVE = '1'
) TT ON TT.HOUSEHOLD_LEVEL_CODE LIKE CONCAT(D.ADDRESS_LEVEL_CODE,'%') OR TT.RESIDENCE_LEVEL_CODE LIKE CONCAT(D.ADDRESS_LEVEL_CODE,'%')
WHERE D.LEVEL_DEPTH <> '6'
AND D.IS_ACTIVE = '1'
GROUP BY D.ADDRESS_LEVEL_CODE
  • 写回答

4条回答 默认 最新

  • _BenChen 2015-12-04 02:30
    关注

    SELECT ADDRESS_LEVEL_CODE
    , COUNT(UUID) AS BE_CHECK_WOMEN
    FROM
    ( SELECT
    D.ADDRESS_LEVEL_CODE
    , TT.UUID
    FROM DIM_ADDRESS D
    LEFT JOIN
    (
    SELECT T.UUID,T.HOUSEHOLD_LEVEL_CODE,T.RESIDENCE_LEVEL_CODE
    FROM DIM_RESIDENT T
    WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) >= 15
    AND FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) <= 50
    AND T.GENDER_CODE = '2'
    AND T.IS_ACTIVE = '1'
    ) TT ON TT.HOUSEHOLD_LEVEL_CODE LIKE CONCAT(D.ADDRESS_LEVEL_CODE,'%')
    WHERE D.LEVEL_DEPTH <> '6'
    AND D.IS_ACTIVE = '1'
    UNION ALL
    SELECT
    D.ADDRESS_LEVEL_CODE
    , TT.UUID
    FROM DIM_ADDRESS D
    LEFT JOIN
    (
    SELECT T.UUID,T.HOUSEHOLD_LEVEL_CODE,T.RESIDENCE_LEVEL_CODE
    FROM DIM_RESIDENT T
    WHERE FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) >= 15
    AND FLOOR(MONTHS_BETWEEN(SYSDATE, T.BIRTH_DATE) / 12) <= 50
    AND T.GENDER_CODE = '2'
    AND T.IS_ACTIVE = '1'
    ) TT ON TT.RESIDENCE_LEVEL_CODE LIKE CONCAT(D.ADDRESS_LEVEL_CODE,'%')
    WHERE D.LEVEL_DEPTH <> '6'
    AND D.IS_ACTIVE = '1'
    ) tmp
    GROUP BY ADDRESS_LEVEL_CODE

    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题