ORCALE执行效率只TO_CHAR大坑
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 会丢失索引 仅此记录
等风来,不如追风去。