帮我优化这段sql,使得查询高效SELECT t.TELEPHONE_NUMBER,
t.STATIC_IP,
t.FRAME_IP,
t.FRAME_ROUTE,
t.FRAME_ROUTE_1,
t.FRAME_ROUTE_2,
CASE WHEN (t.analyze_result = '1' AND CHECK_IP_VALIDITY(FRAME_IP) = '1'
and CHECK_IP_VALIDITY(FRAME_ROUTE) ='1'
and CHECK_IP_VALIDITY(FRAME_ROUTE_1) ='1'
and CHECK_IP_VALIDITY(FRAME_ROUTE_2) ='1'
and (FRAME_IP || FRAME_ROUTE || FRAME_ROUTE_1 || FRAME_ROUTE_2 != '9999')) THEN '1'
ELSE
case when STATIC_IP is null then '3' else '2' end
END AS analyze_result
FROM (
SELECT TELEPHONE_NUMBER,STATIC_IP,
NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-IP-Address=\s*([^,]+)', 1, 1, NULL, 1)),'9') FRAME_IP,
NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 1, NULL, 1)),'9') FRAME_ROUTE,
NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 2, NULL, 1)),'9') FRAME_ROUTE_1,
NVL(TRIM(REGEXP_SUBSTR(STATIC_IP, 'Framed-Route=\s*([^ ]+)', 1, 3, NULL, 1)),'9') FRAME_ROUTE_2,
CASE WHEN REGEXP_LIKE(STATIC_IP, 'Framed-IP-Address=|Framed-Route=') THEN '1' ELSE '2' END AS analyze_result
FROM TI_BROADBAND_NIB
WHERE DAY_ID='20230601' AND TELEPHONE_NUMBER='07231-299142' AND UPPER(STATUS) NOT IN ('INPROGRESS', 'MARKED FOR DELETION', 'SUSPEND', 'BLOCKED DUE TO SECURITY REASONS.')
) t
扫一扫
1593
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
