帮我优化这段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
时间: 2024-02-07 14:02:40 浏览: 179
你可以尝试优化这段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代码示例:
```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;
```
请注意,这只是一个示例,并不代表一定能提高查询效率。你可以根据具体情况进行调整和测试。记得在使用之前备份原始数据,并在生产环境中谨慎使用。
阅读全文