sql - 在 Oracle 中使用 datediff 函数时查询慢
问题描述
作为我的标题,我有以下代码:
SELECT
*
FROM
de.Department
WHERE
de.flag = 1
AND de.DepartmentNum IN (10,4)
AND de.status IN (0,-1,100)
AND datediff('dd',de.datequit,'30-SEP-19') > 9
datediff 函数使我的查询运行速度非常慢(11 条记录需要 16 秒),而且成本也非常高(~43k)。
这是我的 datediff 函数代码
create or replace FUNCTION DATEDIFF
(
P_TYPE_DATE IN VARCHAR2
, P_START_DATE IN TIMESTAMP
, P_END_DATE IN TIMESTAMP
) RETURN NUMBER AS
v_Result NUMBER := -1;
BEGIN
IF P_TYPE_DATE IS NOT NULL AND P_START_DATE IS NOT NULL AND P_END_DATE IS NOT NULL THEN
CASE UPPER(P_TYPE_DATE)
WHEN 'DD' THEN RETURN ROUND(TRUNC(P_END_DATE,'DD') - TRUNC(P_START_DATE,'DD'),0);
WHEN 'HH' THEN RETURN ROUND((TRUNC(P_END_DATE,'HH') - TRUNC(P_START_DATE,'HH')) * 24,0);
WHEN 'MI' THEN RETURN ROUND((TRUNC(P_END_DATE,'MI') - TRUNC(P_START_DATE,'MI')) * 24 * 60,0);
WHEN 'SS' THEN RETURN ROUND((TRUNC(P_END_DATE,'MI') - TRUNC(P_START_DATE,'MI')) * 24 * 60 * 60 + extract(second from (P_END_DATE - P_START_DATE)),0);
ELSE RETURN NULL;
END CASE;
END IF;
RETURN NULL;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END DATEDIFF;
我使用 SELECT * 是因为我想在 Department 表中获取几乎列,所以如果我 SELECT 一些我需要的列,它就不会再改变了。
我可以重写以提高性能和成本吗?
梅尼谢谢!
解决方案
我在 SQL 中创建了一个名为 datediff 的函数作为 datediff 函数,先生
不要使用自定义函数,因为它们会阻止 Oracle 在列上使用索引;而是将列与静态值进行比较:
SELECT *
FROM Department
WHERE flag = 1
AND DepartmentNum IN (10,4)
AND status IN (0,-1,100)
AND datequit > DATE '2019-09-30' + INTERVAL '9' DAY
或者
AND datequit > DATE '2019-09-30' + NUMTODSINTERVAL( 9, 'DAY' )
或者
AND datequit > DATE '2019-09-30' + 9
这是我的 datediff 函数代码
... WHEN 'DD' THEN RETURN ROUND(TRUNC(P_END_DATE,'DD') - TRUNC(P_START_DATE,'DD'),0); ...
如果您想进行与使用TRUNC
忽略时间分量的等效比较,则从使用大于比较更改为使用大于或等于,并将一个时间单位(在您的示例中为天)添加到预期差异。例如:
SELECT *
FROM Department
WHERE flag = 1
AND DepartmentNum IN (10,4)
AND status IN (0,-1,100)
AND datequit >= DATE '2019-09-30' + INTERVAL '10' DAY
推荐阅读
- angular - @HostListener 在 Angular 指令上使用时不会触发
- reactjs - 用于跟踪 history.location.search 的 useMemo 在测试环境中未触发
- php - 您的系统中缺少 PHP 扩展 mbstring
- swift - 让 NSButton 标题比它的图像宽
- android - 如何为用户选择更改 android studio 中项目的显示名称?
- sql-server - 如何将错误消息返回到我的存储过程?
- php - Laravel 5.8:你能帮我解决这个问题吗
- python - 如何打印过滤结果所在的行?
- javascript - 无法显示自举模式,只有背景
- python - 如何更改 tkinter 窗口标题的颜色而不将其从任务栏中删除?