Oracle SQL复杂CASE语句性能优化求助

开发者阿泽

2026-7-6

Oracle SQL 查询性能优化方案

问题核心

两个CTE单独执行耗时均小于10秒，但在SERVICE_USAGE中添加CASE+EXISTS逻辑后，查询耗时骤增至10分钟以上，同时存在冗余的时间判断逻辑。需求是通过匹配房间号、排班日期，以及病例计划/实际开始时间与红/蓝房间时段的重叠关系，完成病例标记。

优化方案

1. 替换冗余的EXISTS子查询为LEFT JOIN+聚合

原SQL中两次EXISTS子查询逻辑完全重复，会对BLOCK_ALLOCATION执行两次全表扫描，改为一次LEFT JOIN并聚合结果，大幅减少IO开销：

SERVICE_USAGE AS (
SELECT
    SUB.*,
    -- 一次聚合获取红/蓝房间标记
    MAX(CASE 
        WHEN BA.SERVICE = 'Blue Room' THEN 'Blue Room'
        WHEN BA.SERVICE = 'Red Room' THEN 'Red Room'
    END) AS RED_BLUE_ROOM
FROM (
    SELECT
        OL.LOG_ID
        , OL.CASE_ID
        , OL.LOG_NAME
        , LOC.LOC_NAME AS LOCATION
        , CS2.PROV_NAME AS ROOM_NM
        , OC.ADD_ON_CASE_YN
        , ZC.NAME AS STATUS_LOG
        , ZOCC.NAME AS CASE_CLASS
        , PC.NAME AS PAT_CLASS
        , OL.SURGERY_DATE
        , CASE
            WHEN ZOS.NAME = 'Obstetrics' THEN 'Gynecology'
            WHEN ZOS.NAME LIKE '%Robotics%' THEN 'Robotics'
            ELSE ZOS.NAME 
          END AS SERVICE
        , CS.PROV_NAME AS SURGEON
        , OP.PROC_NAME AS PRIMARY_PROCEDURE
        , OC.TIME_SCHEDULED
        , VLTE.PATIENT_IN_ROOM_DTTM AS ROOM_IN_DTTM
        , VLTE.PATIENT_OUT_ROOM_DTTM AS ROOM_OUT_DTTM
        , VLTE.MINUTES_IN_ROOM_TO_OUT_ROOM AS MINUTES_IN_OR
        , CASE
            WHEN VCRT.ROOM_OUT_TO_IN IS NULL OR VCRT.ROOM_OUT_TO_IN < 0 THEN 0
            WHEN VCRT.ROOM_OUT_TO_IN > 60 THEN 60
            ELSE VCRT.ROOM_OUT_TO_IN
          END AS ROOM_OUT_TO_IN
     , ROW_NUMBER() OVER (PARTITION BY OL.LOG_ID ORDER BY OLAS.LINE) AS RWNUM
     , 'CASE' AS BLOCK_CASE
     FROM 
        OR_LOG OL
        LEFT OUTER JOIN ZC_OR_STATUS ZC ON OL.STATUS_C = ZC.STATUS_C
        LEFT OUTER JOIN ZC_OR_CASE_CLASS ZOCC ON OL.CASE_CLASS_C = ZOCC.CASE_CLASS_C
        LEFT OUTER JOIN OR_LOG_ALL_SURG OLAS ON OL.LOG_ID = OLAS.LOG_ID AND OLAS.ROLE_C = 1 AND OLAS.PANEL = 1
        LEFT OUTER JOIN OR_LOG_ALL_PROC OLAP ON OL.LOG_ID = OLAP.LOG_ID AND OLAP.LINE = 1
        LEFT OUTER JOIN V_LOG_TIMING_EVENTS VLTE ON OL.LOG_ID = VLTE.LOG_ID
        LEFT OUTER JOIN V_CASE_ROOM_TURNOVER VCRT ON OL.LOG_ID = VCRT.PRE_LOG_ID
        LEFT OUTER JOIN OR_CASE OC ON OL.LOG_ID = OC.LOG_ID 
        LEFT OUTER JOIN ZC_OR_SERVICE ZOS ON OLAS.SERVICE_C = ZOS.SERVICE_C
        LEFT OUTER JOIN CLARITY_SER CS ON OLAS.SURG_ID = CS.PROV_ID
        LEFT OUTER JOIN OR_PROC OP ON OLAP.OR_PROC_ID = OP.OR_PROC_ID
        LEFT OUTER JOIN CLARITY_LOC LOC ON OC.LOC_ID = LOC.LOC_ID
        LEFT OUTER JOIN ZC_PAT_CLASS PC ON OL.PAT_TYPE_C = PC.ADT_PAT_CLASS_C
        LEFT OUTER JOIN CLARITY_SER CS2 ON OL.ROOM_ID = CS2.PROV_ID
     WHERE
        OL.STATUS_C IN (2, 3, 5)
        AND TRUNC(OL.SURGERY_DATE, 'Year') > TRUNC(SYSDATE, 'yyyy') - INTERVAL '2' YEAR
        AND OL.SURGERY_DATE <= LAST_DAY(ADD_MONTHS(SYSDATE,-1))
        AND TRUNC(OL.SURGERY_DATE) - TRUNC(OL.SURGERY_DATE, 'IW') < 5
        AND OC.LOC_ID IN (12194, 121122248)
        -- 提前过滤后续需要的条件，减少数据集
        AND OC.TIME_SCHEDULED < TRUNC(OC.TIME_SCHEDULED) + INTERVAL '17:00:00' HOUR TO SECOND
        AND VLTE.MINUTES_IN_ROOM_TO_OUT_ROOM IS NOT NULL
) SUB
-- 提前过滤红/蓝房间数据，减少JOIN量级
LEFT JOIN (
    SELECT SCHEDULE_DATE, ROOM_NM, SERVICE, SLOT_START_TIME, SLOT_END_TIME
    FROM BLOCK_ALLOCATION
    WHERE SERVICE IN ('Blue Room', 'Red Room')
) BA ON BA.SCHEDULE_DATE = SUB.SURGERY_DATE 
    AND BA.ROOM_NM = SUB.ROOM_NM
    -- 简化时间判断逻辑，与业务规则对齐
    AND (
        (SUB.ROOM_IN_DTTM >= BA.SLOT_START_TIME AND SUB.TIME_SCHEDULED < BA.SLOT_END_TIME)
        OR (SUB.TIME_SCHEDULED >= BA.SLOT_START_TIME AND SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME)
    )
WHERE SUB.RWNUM = 1
  AND SUB.SERVICE != 'Gastroenterology'
GROUP BY SUB.LOG_ID, SUB.CASE_ID, SUB.LOG_NAME, SUB.LOCATION, SUB.ROOM_NM, SUB.ADD_ON_CASE_YN, SUB.STATUS_LOG, SUB.CASE_CLASS, SUB.PAT_CLASS, SUB.SURGERY_DATE, SUB.SERVICE, SUB.SURGEON, SUB.PRIMARY_PROCEDURE, SUB.TIME_SCHEDULED, SUB.ROOM_IN_DTTM, SUB.ROOM_OUT_DTTM, SUB.MINUTES_IN_OR, SUB.ROOM_OUT_TO_IN, SUB.RWNUM, SUB.BLOCK_CASE
)

2. 移除不必要的ORDER BY

删除BLOCK_ALLOCATION中的ORDER BY 1,2：CTE内的排序不会传递到后续查询，只会增加额外排序开销。
删除SUB子查询中的ORDER BY OL.SURGERY_DATE DESC：排序逻辑已整合到ROW_NUMBER的ORDER BY中，子查询排序无意义。

3. 添加针对性索引

根据过滤和JOIN条件创建索引，加速数据检索：

-- 针对V_OR_ROOM_TEMPLATE的过滤与JOIN条件
CREATE INDEX IDX_VORT_DATE_ROOM_SERVICE ON V_OR_ROOM_TEMPLATE(SCHEDULE_DATE, ROOM_ID, SERVICE_C);
-- 针对OR_LOG的核心过滤条件
CREATE INDEX IDX_ORLOG_DATE_STATUS_LOC ON OR_LOG(SURGERY_DATE, STATUS_C, LOC_ID);
-- 加速OR_LOG与关联表的JOIN
CREATE INDEX IDX_ORLAS_LOG_ROLE_PANEL ON OR_LOG_ALL_SURG(LOG_ID, ROLE_C, PANEL);
CREATE INDEX IDX_ORLAP_LOG_LINE ON OR_LOG_ALL_PROC(LOG_ID, LINE);

4. 优化UNION ALL分支

确保UNION ALL两个分支的列类型完全一致，避免隐式类型转换。
保留BLOCK_ALLOCATION中ASSIGNED_MINS_IN_OR <= 570的过滤条件，减少第二个分支的数据量。

完整优化后SQL

WITH BLOCK_ALLOCATION AS (
    SELECT 
        SCHEDULE_DATE
        , CS.PROV_NAME AS ROOM_NM
        , CASE
            WHEN ROOM_ID IN ('150339507', '150339508', '150339509', '150339510', '150339512', '150339513')
            THEN 'AMBULATORY CENTER'
            ELSE 'MAIN OPERATING ROOM'
         END AS LOCATION
        , SLOT_START_TIME
        , SLOT_END_TIME
        , CASE 
            WHEN SLOT_END_TIME > SLOT_START_TIME 
            THEN ROUND(((SLOT_END_TIME - SLOT_START_TIME)*24)*60, 1) 
            ELSE ROUND((((SLOT_END_TIME+1)-SLOT_START_TIME)*24)*60, 1)
          END AS ASSIGNED_MINS_IN_OR
        , CASE
            WHEN ZC.NAME LIKE '%Robotics%'
            THEN 'Robotics'
            ELSE ZC.NAME
          END AS SERVICE
        , 'BLOCK' AS BLOCK_CASE
    FROM V_OR_ROOM_TEMPLATE RT
         LEFT OUTER JOIN CLARITY_SER CS ON RT.ROOM_ID = CS.PROV_ID
         LEFT OUTER JOIN ZC_OR_SERVICE ZC ON RT.SERVICE_C = ZC.SERVICE_C
    WHERE
         TRUNC(SCHEDULE_DATE, 'Year') > TRUNC(SYSDATE, 'yyyy') - INTERVAL '2' YEAR
         AND SCHEDULE_DATE <= LAST_DAY(ADD_MONTHS(SYSDATE,-1))
         AND TRUNC(SCHEDULE_DATE) - TRUNC(SCHEDULE_DATE, 'IW') < 5
         AND ROOM_ID IN ('150123464', '150123465', '150123466', '150123467', '150123468', '150123469', '150123470', '150123471', '150123472', '150137692', '150135968', '150123474', '150339507', '150339508', '150339509', '150339510', '150339512', '150339513')
),

SERVICE_USAGE AS (
SELECT
    SUB.*,
    MAX(CASE 
        WHEN BA.SERVICE = 'Blue Room' THEN 'Blue Room'
        WHEN BA.SERVICE = 'Red Room' THEN 'Red Room'
    END) AS RED_BLUE_ROOM
FROM (
    SELECT
        OL.LOG_ID
        , OL.CASE_ID
        , OL.LOG_NAME
        , LOC.LOC_NAME AS LOCATION
        , CS2.PROV_NAME AS ROOM_NM
        , OC.ADD_ON_CASE_YN
        , ZC.NAME AS STATUS_LOG
        , ZOCC.NAME AS CASE_CLASS
        , PC.NAME AS PAT_CLASS
        , OL.SURGERY_DATE
        , CASE
            WHEN ZOS.NAME = 'Obstetrics' THEN 'Gynecology'
            WHEN ZOS.NAME LIKE '%Robotics%' THEN 'Robotics'
            ELSE ZOS.NAME 
          END AS SERVICE
        , CS.PROV_NAME AS SURGEON
        , OP.PROC_NAME AS PRIMARY_PROCEDURE
        , OC.TIME_SCHEDULED
        , VLTE.PATIENT_IN_ROOM_DTTM AS ROOM_IN_DTTM
        , VLTE.PATIENT_OUT_ROOM_DTTM AS ROOM_OUT_DTTM
        , VLTE.MINUTES_IN_ROOM_TO_OUT_ROOM AS MINUTES_IN_OR
        , CASE
            WHEN VCRT.ROOM_OUT_TO_IN IS NULL OR VCRT.ROOM_OUT_TO_IN < 0 THEN 0
            WHEN VCRT.ROOM_OUT_TO_IN > 60 THEN 60
            ELSE VCRT.ROOM_OUT_TO_IN
          END AS ROOM_OUT_TO_IN
     , ROW_NUMBER() OVER (PARTITION BY OL.LOG_ID ORDER BY OLAS.LINE) AS RWNUM
     , 'CASE' AS BLOCK_CASE
     FROM 
        OR_LOG OL
        LEFT OUTER JOIN ZC_OR_STATUS ZC ON OL.STATUS_C = ZC.STATUS_C
        LEFT OUTER JOIN ZC_OR_CASE_CLASS ZOCC ON OL.CASE_CLASS_C = ZOCC.CASE_CLASS_C
        LEFT OUTER JOIN OR_LOG_ALL_SURG OLAS ON OL.LOG_ID = OLAS.LOG_ID AND OLAS.ROLE_C = 1 AND OLAS.PANEL = 1
        LEFT OUTER JOIN OR_LOG_ALL_PROC OLAP ON OL.LOG_ID = OLAP.LOG_ID AND OLAP.LINE = 1
        LEFT OUTER JOIN V_LOG_TIMING_EVENTS VLTE ON OL.LOG_ID = VLTE.LOG_ID
        LEFT OUTER JOIN V_CASE_ROOM_TURNOVER VCRT ON OL.LOG_ID = VCRT.PRE_LOG_ID
        LEFT OUTER JOIN OR_CASE OC ON OL.LOG_ID = OC.LOG_ID 
        LEFT OUTER JOIN ZC_OR_SERVICE ZOS ON OLAS.SERVICE_C = ZOS.SERVICE_C
        LEFT OUTER JOIN CLARITY_SER CS ON OLAS.SURG_ID = CS.PROV_ID
        LEFT OUTER JOIN OR_PROC OP ON OLAP.OR_PROC_ID = OP.OR_PROC_ID
        LEFT OUTER JOIN CLARITY_LOC LOC ON OC.LOC_ID = LOC.LOC_ID
        LEFT OUTER JOIN ZC_PAT_CLASS PC ON OL.PAT_TYPE_C = PC.ADT_PAT_CLASS_C
        LEFT OUTER JOIN CLARITY_SER CS2 ON OL.ROOM_ID = CS2.PROV_ID
     WHERE
        OL.STATUS_C IN (2, 3, 5)
        AND TRUNC(OL.SURGERY_DATE, 'Year') > TRUNC(SYSDATE, 'yyyy') - INTERVAL '2' YEAR
        AND OL.SURGERY_DATE <= LAST_DAY(ADD_MONTHS(SYSDATE,-1))
        AND TRUNC(OL.SURGERY_DATE) - TRUNC(OL.SURGERY_DATE, 'IW') < 5
        AND OC.LOC_ID IN (12194, 121122248)
        AND OC.TIME_SCHEDULED < TRUNC(OC.TIME_SCHEDULED) + INTERVAL '17:00:00' HOUR TO SECOND
        AND VLTE.MINUTES_IN_ROOM_TO_OUT_ROOM IS NOT NULL
) SUB
LEFT JOIN (
    SELECT SCHEDULE_DATE, ROOM_NM, SERVICE, SLOT_START_TIME, SLOT_END_TIME
    FROM BLOCK_ALLOCATION
    WHERE SERVICE IN ('Blue Room', 'Red Room')
) BA ON BA.SCHEDULE_DATE = SUB.SURGERY_DATE 
    AND BA.ROOM_NM = SUB.ROOM_NM
    AND (
        (SUB.ROOM_IN_DTTM >= BA.SLOT_START_TIME AND SUB.TIME_SCHEDULED < BA.SLOT_END_TIME)
        OR (SUB.TIME_SCHEDULED >= BA.SLOT_START_TIME AND SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME)
    )
WHERE SUB.RWNUM = 1
  AND SUB.SERVICE != 'Gastroenterology'
GROUP BY SUB.LOG_ID, SUB.CASE_ID, SUB.LOG_NAME, SUB.LOCATION, SUB.ROOM_NM, SUB.ADD_ON_CASE_YN, SUB.STATUS_LOG, SUB.CASE_CLASS, SUB.PAT_CLASS, SUB.SURGERY_DATE, SUB.SERVICE, SUB.SURGEON, SUB.PRIMARY_PROCEDURE, SUB.TIME_SCHEDULED, SUB.ROOM_IN_DTTM, SUB.ROOM_OUT_DTTM, SUB.MINUTES_IN_OR, SUB.ROOM_OUT_TO_IN, SUB.RWNUM, SUB.BLOCK_CASE
),

BLOCK_CASES as (
SELECT 
    LOG_ID
    , LOG_NAME
    , LOCATION
    , ROOM_NM
    , ADD_ON_CASE_YN
    , STATUS_LOG
    , CASE_CLASS
    , PAT_CLASS
    , SURGERY_DATE
    , TIME_SCHEDULED
    , ROOM_IN_DTTM
    , ROOM_OUT_DTTM
    , MINUTES_IN_OR
    , SERVICE
    , SURGEON
    , NULL AS ASSIGNED_MINS_IN_OR
    , PRIMARY_PROCEDURE
    , ROOM_OUT_TO_IN
    , BLOCK_CASE
    , RED_BLUE_ROOM
FROM 
    SERVICE_USAGE
UNION ALL
SELECT
    NULL AS LOG_ID
    , NULL AS LOG_NAME
    , LOCATION
    , ROOM_NM
    , NULL AS ADD_ON_CASE_YN
    , NULL AS STATUS_LOG
    , NULL AS CASE_CLASS
    , NULL AS PAT_CLASS
    , SCHEDULE_DATE AS SURGERY_DATE
    , NULL AS TIME_SCHEDULED
    , SLOT_START_TIME AS ROOM_IN_DTTM
    , SLOT_END_TIME AS ROOM_OUT_DTTM
    , NULL AS MINUTES_IN_OR
    , SERVICE
    , NULL AS SURGEON
    , ASSIGNED_MINS_IN_OR
    , NULL AS PRIMARY_PROCEDURE
    , NULL AS ROOM_OUT_TO_IN
    , BLOCK_CASE
    , NULL AS RED_BLUE_ROOM
FROM 
    BLOCK_ALLOCATION
WHERE
    ASSIGNED_MINS_IN_OR <= 570
)
SELECT * FROM BLOCK_CASES

Oracle SQL复杂CASE语句性能优化求助

问题核心 #

优化方案 #

1. 替换冗余的EXISTS子查询为LEFT JOIN+聚合 #

2. 移除不必要的ORDER BY #

3. 添加针对性索引 #

4. 优化UNION ALL分支 #

完整优化后SQL #