各位大神,小弟最近在执行以下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