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

Question

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

Answer 1

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