ORCALE执行效率只TO_CHAR大坑

2024-01-13 18:50:40

1.我在优化代码时碰到SQL语句执行缓慢的问题，最后定位到了这个方法

MED_OPERATION_SCHEDULE 表中含有50万条数据

CREATE OR REPLACE FUNCTION FUN_GET_ANES_LOAD(USERID       VARCHAR2,
                                             SCHEDULEDATE DATE)
  RETURN VARCHAR2 IS
  HASSCHEDULEDTIME VARCHAR2(20);
BEGIN
  SELECT SUM(OS.OPERATING_TIME)
    INTO HASSCHEDULEDTIME
    FROM MED_OPERATION_SCHEDULE OS
   WHERE TO_CHAR(OS.SCHEDULED_DATE_TIME, ‘YYYY-MM-DD‘) =
         TO_CHAR(SCHEDULEDATE, ‘YYYY-MM-DD‘)
     AND (OS.ANESTHESIA_DOCTOR = USERID OR OS.ANESTHESIA_ASSISTANT = USERID OR
          OS.SECOND_ANESTHESIA_ASSISTANT = USERID OR
          OS.THIRD_ANESTHESIA_ASSISTANT = USERID OR
          OS.FOURTH_ANESTHESIA_ASSISTANT = USERID)
     AND OS.STATE <> -1;
  RETURN HASSCHEDULEDTIME;
END FUN_GET_ANES_LOAD;

2.整个方法执行下来要14秒多严重影响系统效率，后经指点代码优化

CREATE OR REPLACE FUNCTION FUN_GET_NURSE_LOAD
(
USERID       VARCHAR2,
SCHEDULEDATE DATE
)
  RETURN VARCHAR2 IS
  HASSCHEDULEDTIME VARCHAR2(20);
BEGIN
  SELECT SUM(OS.OPERATING_TIME)
    INTO HASSCHEDULEDTIME
    FROM MED_OPERATION_SCHEDULE OS
   WHERE OS.SCHEDULED_DATE_TIME > SCHEDULEDATE 
   and OS.SCHEDULED_DATE_TIME < TO_DATE(TO_CHAR(SCHEDULEDATE,‘YYYY-MM-DD‘) ||‘ 23:59:59‘,‘yyyy-mm-dd hh24:mi:ss‘)
     AND (OS.FIRST_OPERATION_NURSE = USERID OR
          OS.SECOND_OPERATION_NURSE = USERID OR
          OS.THIRD_OPERATION_NURSE = USERID)

      AND OS.STATE <> -1;
  RETURN HASSCHEDULEDTIME;
END FUN_GET_NURSE_LOAD;

改变的代码是

TO_CHAR(OS.SCHEDULED_DATE_TIME, ‘YYYY-MM-DD‘) =
         TO_CHAR(SCHEDULEDATE, ‘YYYY-MM-DD‘)


变更为 

 OS.SCHEDULED_DATE_TIME > SCHEDULEDATE 
   and OS.SCHEDULED_DATE_TIME < TO_DATE(TO_CHAR(SCHEDULEDATE,‘YYYY-MM-DD‘) ||‘ 23:59:59‘,‘yyyy-mm-dd hh24:mi:ss‘)

代码效率一下子提升只需要执行零点几秒

后来才知道 to_char 会丢失索引仅此记录

ORCALE执行效率只TO_CHAR大坑

码农公寓

相关文章