WHERE子句中的Oracle标量函数导致性能较差

Question

我已经编写了一个将文本值转换为日期/时间的标量函数（DYNAMIC_DATE）。例如，DYANMIC_DATE('T-1')（T-1 =今天减1 ='昨天'）返回08-AUG-2012 00:00:00。它也接受日期字符串：DYNAMIC_DATE('10/10/1974')。

该函数利用CASE语句解析唯一参数并计算相对于sysdate的日期。

虽然它并没有使用在其模式中的任何表，它利用TABLE类型的存储日期格式字符串：

TYPE VARCHAR_TABLE IS TABLE OF VARCHAR2(10); 
formats VARCHAR_TABLE := VARCHAR_TABLE ('mm/dd/rrrr','mm-dd-rrrr','rrrr/mm/dd','rrrr-mm-dd'); 

当我使用SELECT子句中的功能，在<1秒查询返回：

SELECT DYNAMIC_DATE('MB-1') START_DATE, DYNAMIC_DATE('ME-1') END_DATE 
FROM DUAL 

如果我用它对付我们的约会维度表（共91311条记录），查询完成了<1秒：

SELECT count(1) 
from date_dimension 
where calendar_dt between DYNAMIC_DATE('MB-1') and DYNAMIC_DATE('ME-1') 

其他人，但是，如果它是用来对付一个更大的表正与功能问题（26301317个记录）：

/* 
cost: 148,840 
records: 151,885 
time: ~20 minutes 
*/ 
SELECT count(1) 
FROM ORDERS ord 
WHERE trunc(ord.ordering_date) between DYNAMIC_DATE('mb-1') and DYNAMIC_DATE('me-1') 

然而，相同的查询，使用“硬编码”日期，退货相当迅速：

/* 
cost: 144,257 
records: 151,885 
time: 62 seconds 
*/ 
SELECT count(1) 
FROM ORDERS ord 
WHERE trunc(ord.ordering_date) between to_date('01-JUL-2012','dd-mon-yyyy') AND to_date('31-JUL-2012','dd-mon-yyyy') 

供应商的香草安装不包括在ORDERING_DATE领域的指标。

的解释这两个查询计划是相似的：

与功能：

通过硬编码日期：

• 是在DYNAMIC_DATE功能被在WHERE一再呼吁条款？
• 还有什么可以解释这种差距？

** **编辑

甲NONUNIQUE索引加入到订单表。两个查询都在1秒内执行<。两种方案都是一样的（方法），但具有该功能的方案成本较低。

我从功能中删除了DETERMINISTIC关键字;查询在1秒内​​执行<。

• 问题是真的与函数还是与表相关？
• 从现在开始的3年，这张表甚至更大时，如果我不包含关键字DETERMINISTIC，查询性能会受损吗？
• DETERMINISTIC关键字对函数结果有任何影响吗？如果我明天运行DYNAMIC_DATE('T-1')，我是否会得到与今天运行时相同的结果（08/09/2012）？如果是这样，这种方法将无法工作。

Answer 1

如果计划的步骤是相同的​​，那么完成的工作量应该是相同的。如果您追踪会话（如SQL * Plus中的set autotrace on或类似事件10046跟踪的更复杂的东西），或者如果您在DBA_HIST_SQLSTAT（假设您已获得AWR表的许可访问权）中查看，您是否看到（大致）相同的数量两个查询的逻辑I/O和CPU消耗是多少？运行第二个查询时，您看到的运行时差异是否可能是数据缓存的结果？

Answer 2

我猜测问题不在于你的功能。尝试在trunc(ord.ordering_date)上创建基于功能的索引并查看解释计划。

CREATE INDEX ord_date_index ON ord(trunc(ord.ordering_date));