建议APPLYDATE,PERSONNUM,PERSONID字段创建索引
解决方案 »
- sqlload函数的使用问题 求解
- 函数调用者是谁?
- 关于SELECT的索引问题
- 急!用union连接后的多表查询的问题?
- 关于ORA-01000: maximum open cursors exceeded
- 怎么样导出多个表的sql建表语句?
- 求一个SQL语句:
- 为什么在ORACLE 8.1.6中,建立了数据库链接DBLINK,测试时,提示:数据库链路未处于活动状态。不知道问题出在哪里?
- sql語句,請教......
- oracle所有管理员账户都不能登陆
- ORACLE cast函数 将日期转换为字符串问题
- ORA-29540: 类 oracle/jpub/runtime/dbws/DbwsProxy 不存在
这个如何解决呢?
CREATE OR REPLACE FUNCTION OFILM_FN_SHIFTENDTIME --标准下班时间
(P_NUM VARCHAR2,
P_DATE DATE)
RETURN DATE IS
ENDTIME DATE;
BEGIN
SELECT SHIFTENDDATE INTO ENDTIME
FROM VP_SCHEDULE
WHERE
TO_CHAR(SHIFTENDDATE,'YYYY/MM/DD')=TO_CHAR(P_DATE,'YYYY/MM/DD')
AND PERSONNUM = P_NUM
AND ROWNUM=1;
RETURN ENDTIME;
EXCEPTION
WHEN OTHERS THEN
RETURN '';
END ;
TO_CHAR转换导致的么?
改为
select max()避免异常就可以了
另外VP_SCHEDULE是怎么个数量级?可以考虑在这个表上适当增加索引
是一个三四万人的考勤系统,schedule是他们的排班表 我那个函数是查出他们的排班的下班时间
S.PERSONNUM AS 工号,
PP.PERSONFULLNAME AS 姓名,
PP.HOMELABORLEVELDSC3 AS 人员类别,
CUX_KRONOS_PKG.GET_HIGHER_DEPT(PP.HOMELABORLEVELDSC2, '4' ) AS 事业本部,
PP.HOMELABORLEVELDSC2 AS 部门,
ofilm_fn_get_personcusdata(S.PERSONNUM, '职务' ) AS 职务,
ofilm_fn_get_personcusdata(S.PERSONNUM, '职级' ) AS 职级,
ofilm_fn_get_personcusdata(S.PERSONNUM, '工序' ) AS 工序,
OFILM_FN_COMENTDSC(S.PERSONNUM, S.EVENTDATE) AS 班别,
TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' ) AS 日期,
TO_CHAR(S.INPUNCHDTM,'HH24:MI') AS 上班卡,
TO_CHAR(S.OUTPUNCHDTM,'HH24:MI') AS 下班卡,
TO_CHAR(VAS.SHIFTENDDATE,'HH24:MI') AS 标准下班时间,
OFILM_FN_STATUS(S.PERSONNUM, S.EVENTDATE) || S.PAYCODENAME AS 出勤状态,
CASE WHEN (S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24>0
THEN ROUND((S.OUTPUNCHDTM-VAS.SHIFTENDDATE)*24,2)
ELSE 0 END 加班时间
FROM ( SELECT PERSONNUM,
PAYCODENAME,
EVENTDATE,
INPUNCHDTM,
OUTPUNCHDTM
FROM VP_TIMESHTPUNCHV42
WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe
AND (INPUNCHDTM IS NULL AND OUTPUNCHDTM IS NULL)
AND PAYCODENAME IS NOT NULL
AND TIMESHEETITEMTYPE != 'ManagerJustification'
UNION
SELECT PERSONNUM, PAYCODENAME, EVENTDATE, INPUNCHDTM, OUTPUNCHDTM
FROM VP_TIMESHTPUNCHV42
WHERE EVENTDATE BETWEEN :From_timeframe AND :To_timeframe
AND (INPUNCHDTM IS NOT NULL OR OUTPUNCHDTM IS NOT NULL )
AND PAYCODENAME IS NULL) S
LEFT JOIN VP_ALLPERSONV42 PP
ON S.PERSONNUM = PP.PERSONNUM
LEFT JOIN VP_ASSIGNSCHEDULE VAS
ON S.PERSONNUM=VAS.PERSONNUM AND TO_CHAR(S.EVENTDATE,'YYYY/MM/DD')=TO_CHAR(VAS.SHIFTSTARTDATE,'YYYY/MM/DD')
LEFT JOIN mywtkemployee MWE
on PP.employeeid=MWE.employeeid
WHERE
MWE.SESSIONID= :wtksessionid
ORDER BY S.PERSONNUM, TO_CHAR(S.EVENTDATE, 'YYYY-MM-DD' )这是我修改的语句,用的函数少了,可还是很慢,1.5w条数据需要一个小时,从哪里下手修改呢