帮我优化这段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

你可以尝试优化这段SQL的查询效率。以下是对查询进行优化的建议：

1. 索引优化：确保TI_BROADBAND_NIB表中的DAY_ID、TELEPHONE_NUMBER和STATUS列上都有适当的索引。这将提高WHERE子句的过滤效率。

2. 减少函数调用：在子查询中，可以将CHECK_IP_VALIDITY函数的调用次数减少为一次。你可以将CHECK_IP_VALIDITY(FRAME_IP)、CHECK_IP_VALIDITY(FRAME_ROUTE)、CHECK_IP_VALIDITY(FRAME_ROUTE_1)和CHECK_IP_VALIDITY(FRAME_ROUTE_2)的结果存储到临时变量中，然后在CASE语句中使用这些变量。

3. 使用JOIN代替子查询：将子查询转换为一个JOIN操作，这样可以减少查询次数并提高效率。你可以将子查询的结果存储到一个临时表中，然后与TI_BROADBAND_NIB表进行JOIN操作。

下面是优化后的SQL代码示例：

WITH temp AS (
    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,
           CHECK_IP_VALIDITY(FRAME_IP) AS ip_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE) AS route_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE_1) AS route_1_validity,
           CHECK_IP_VALIDITY(FRAME_ROUTE_2) AS route_2_validity
    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.')
)
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 t.ip_validity = '1' and t.route_validity = '1' and t.route_1_validity = '1' and t.route_2_validity = '1' and (t.FRAME_IP || t.FRAME_ROUTE || t.FRAME_ROUTE_1 || t.FRAME_ROUTE_2 != '9999')) THEN '1'
            ELSE CASE WHEN t.STATIC_IP IS NULL THEN '3' ELSE '2' END
       END AS analyze_result
FROM temp t;

请注意，这只是一个示例，并不代表一定能提高查询效率。你可以根据具体情况进行调整和测试。记得在使用之前备份原始数据，并在生产环境中谨慎使用。