SQL Oracle：如何让这种复杂的case语句在性能上更好

yqkkidmi 于 2023-11-17 发布在 Oracle

关注(0)|答案(2)|浏览(105)

我对SQL Oracle和从我们的数据库中提取数据相对较新。我感谢任何帮助或指导。
我有两个CTE：Block Allocation和Service Usage。CTE Block Allocation拉取部门的计划（计划日期、开始时间、结束时间、小时数等），Service_Usage拉取所有已完成的案例。我正在尝试通过匹配房间号、计划日期以及案例是在红/蓝房间的时段开始时间和结束时间之间计划还是开始时间，来标识在红房间或蓝房间中完成的案例。
当我单独运行CTE时，我通常在不到10秒的时间内得到结果。我向CTE_Service_Usage添加了一个case语句，我的查询花费了10多分钟。我不确定是case语句还是结尾的union导致我的查询性能不佳。case语句显然是多余的。
我从我们的数据库中提取数据，因此很难提供样本数据。下面是逻辑示例：
Red Room计划于2023年1月3日7：30至12：30在Room 6完成。Case 1234于2023年1月3日8：30至11：30在Room 6完成。Case 1234被标识为“Red Room”。另一方面，Case 2222计划于11：30开始，但在12：35开始。Case 2222被标识为“Red Room”，尽管Case 2222在Red Room的计划时间段之后开始。
2023年1月4日12：30 - 17：00在2号房间安排了蓝色房间。2023年1月4日14：00 - 16：30在2号房间完成了病例0000。病例0000被标识为“蓝色房间”。病例1111计划在2号房间开始，但在12：25开始（提前5分钟开始）。病例1111仍然被标识为“蓝色房间”。
请让我知道如何更好地证明我的疑问。谢谢！
以下是我的查询：

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')
ORDER BY 1, 2
),

SERVICE_USAGE AS (
SELECT
    SUB.*
    , CASE 
        WHEN EXISTS (
        SELECT 1 
        FROM BLOCK_ALLOCATION BA
        WHERE BA.SCHEDULE_DATE = SUB.SURGERY_DATE 
        AND BA.ROOM_NM = SUB.ROOM_NM
        AND BA.SERVICE = 'Blue Room'
        AND (((SUB.ROOM_IN_DTTM >= BA.SLOT_START_TIME AND (SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME AND SUB.TIME_SCHEDULED < BA.SLOT_END_TIME))) 
            OR (SUB.TIME_SCHEDULED >= BA.SLOT_START_TIME AND (SUB.TIME_SCHEDULED < BA.SLOT_END_TIME AND SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME)))
        ) THEN 'Blue Room' 
        WHEN EXISTS (
        SELECT 1 
        FROM BLOCK_ALLOCATION BA
        WHERE BA.SCHEDULE_DATE = SUB.SURGERY_DATE 
        AND BA.ROOM_NM = SUB.ROOM_NM
        AND BA.SERVICE = 'Red Room'
        AND (((SUB.ROOM_IN_DTTM >= BA.SLOT_START_TIME AND (SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME AND SUB.TIME_SCHEDULED < BA.SLOT_END_TIME))) 
            OR (SUB.TIME_SCHEDULED >= BA.SLOT_START_TIME AND (SUB.TIME_SCHEDULED < BA.SLOT_END_TIME AND SUB.ROOM_IN_DTTM < BA.SLOT_END_TIME)))
        ) THEN 'Red Room' 
        END 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 OL.LOG_ID, 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)
    ORDER BY 
        OL.SURGERY_DATE DESC
    ) SUB
  WHERE
    SUB.RWNUM = 1
),

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
FROM 
    SERVICE_USAGE
WHERE
    TIME_SCHEDULED < TRUNC(TIME_SCHEDULED) + INTERVAL '17:00:00' HOUR TO SECOND
    AND MINUTES_IN_OR IS NOT NULL
    AND SERVICE != 'Gastroenterology'
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
FROM 
    BLOCK_ALLOCATION
WHERE
    ASSIGNED_MINS_IN_OR <= 570
)

字符串

oracle

来源：https://stackoverflow.com/questions/77484242/sql-oracle-how-to-make-this-complex-case-statement-better-in-performance-time

2条答案

按热度按时间

lnxxn5zx1#

我会尝试下面这样的东西：

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')
ORDER BY 1, 2
)
,balloc as (
   SELECT BA.SCHEDULE_DATE, BA.ROOM_NM, BA.SLOT_START_TIME, BA.SLOT_END_TIME
   FROM BLOCK_ALLOCATION BA 
 )
,sub as (
   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 OL.LOG_ID, 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)
    ORDER BY 
        OL.SURGERY_DATE DESC

  )
  ,SERVICE_USAGE AS (
     SELECT
     SUB.*
     ,balloc.service END RED_BLUE_ROOM
   FROM SUB
   left join balloc on (BAlloc.SCHEDULE_DATE = SUB.SURGERY_DATE and BAlloc.ROOM_NM = SUB.ROOM_NM
     AND (((SUB.ROOM_IN_DTTM >= BAlloc.SLOT_START_TIME AND (SUB.ROOM_IN_DTTM < BAlloc.SLOT_END_TIME AND SUB.TIME_SCHEDULED < BAlloc.SLOT_END_TIME))) 
            OR (SUB.TIME_SCHEDULED >= BAlloc.SLOT_START_TIME AND (SUB.TIME_SCHEDULED < BAlloc.SLOT_END_TIME AND SUB.ROOM_IN_DTTM < BAlloc.SLOT_END_TIME))))
    WHERE
    SUB.RWNUM = 1
),

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
FROM 
    SERVICE_USAGE
WHERE
    TIME_SCHEDULED < TRUNC(TIME_SCHEDULED) + INTERVAL '17:00:00' HOUR TO SECOND
    AND MINUTES_IN_OR IS NOT NULL
    AND SERVICE != 'Gastroenterology'
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
  FROM 
    BLOCK_ALLOCATION
  WHERE
    ASSIGNED_MINS_IN_OR <= 570
  )

字符串
;
仍然，不确定是否不需要多一个row_number（）或用一个不同的row_number（）替换前一个row_number（）。没有数据，我无法测试。解释计划也可能有所帮助。

赞(0）回复(0）举报 2023-11-17

ymzxtsji2#

不清楚您的问题是查询性能还是意外结果，在没有示例数据和解释计划的情况下，我已经看到了意外结果。
service_usage CTE用于决定约会是red room还是blue room，但突出显示的两个案例都不是。CASE的两个部分除了房间名称外是相同的，核心代码是：

AND (((sub.room_in_dttm >= ba.slot_start_time AND 
          (sub.room_in_dttm < ba.slot_end_time AND sub.time_scheduled < ba.slot_end_time)))
       OR (sub.time_scheduled >= ba.slot_start_time AND 
              (sub.time_scheduled < ba.slot_end_time AND sub.room_in_dttm < ba.slot_end_time)))

字符串
病例2222原定于11：30（红房间），但在12：35（蓝房间）开始，因此两个房间均不适用。病例1111原定于12：30（蓝房间），但在12：25（红房间）开始，因此两个房间均不适用。
我们没有查询的主要部分，所以我们不知道service_usage CTE的red_blue_room输出会发生什么，但它本质上是未定义的。
我建议删除所有额外的代码，专注于房间代码，使sub成为一个单独的CTE，然后使用类似于以下CTE的代码：

service_room AS (
    SELECT ba.service
    FROM   block_allocation ba
    WHERE  ba.schedule_date = sub.surgery_date
    AND    ba.room_nm = sub.room_nm
    AND (((sub.room_in_dttm >= ba.slot_start_time AND 
              (sub.room_in_dttm < ba.slot_end_time AND sub.time_scheduled < ba.slot_end_time)))
           OR (sub.time_scheduled >= ba.slot_start_time AND 
                  (sub.time_scheduled < ba.slot_end_time AND sub.room_in_dttm < ba.slot_end_time)))

型

赞(0）回复(0）举报 2023-11-17

我来回答

SQL Oracle：如何让这种复杂的case语句在性能上更好

2条答案

相关问题

热门标签

最新问答