Oracle 9i - 优化器与日期算术有关的问题？

Question

我们有一个查询，其中包括（但具有其他联接、表和 where 条件）：

SELECT
    o.contact_id, 
    o.completed_date, 
    o.submitted_date
FROM
    orders o /* 860,000 row table */
WHERE   
    ? <= o.submitted_date
    AND o.submitted_date < ? + 1

从 Java 应用程序调用。

? 参数允许检查两个日期之间提交的订单。

但是，该查询运行速度非常慢。

我们转换为从 PL/SQL 运行进行测试，如下所示：

SubmittedDateFrom date:=to_date('2011-07-15', 'yyyy-mm-dd');
SubmittedDateTo date:=to_date('2011-07-15', 'yyyy-mm-dd');
CURSOR c_orgs    IS    
SELECT
    o.contact_id, 
    o.completed_date, 
    o.submitted_date
FROM
    orders o
WHERE   
    SubmittedDateFrom <= o.submitted_date
    AND o.submitted_date < SubmittedDateTo + 1;
BEGIN
    FOR c_o IN c_orgs LOOP
        DBMS_OUTPUT.put_line('Submitted date = '||c_o.submitted_date);                               
    END LOOP;
END;

如果我们：

1. 将 SubscribedDateTo 值转换为 to_date('2011-07-16', 'yyyy-mm-dd') （即在查询之外进行算术），
2. 将 SubscribedDateTo 设为字符串并使用“to_date('SubscribedDateTo', 'yyyy-mm-dd') + 1" 作为 WHERE 中的第二个条件。

然后，查询速度显着加快（<1 秒 vs. 44+ 秒）。

进一步信息：

• 在查询上运行解释计划会出现错误ORA-00932：不一致的数据类型：期望的DATE得到NUMBER
• subscribed_date列有索引和统计信息等 在 trunc() 调用中包装 SubscribedDateTo + 1运行
• 不会影响性能
• 我们没有具有类似数据量等的非 9i 数据库来测试是否是否是Oracle的版本。

问题是：我们无法找到任何信息明确表明 Oracle 9i 优化器对这种日期运算存在问题。这是这里发生的事情还是还有其他事情发生？

Answer 1

我始终确保所有转换都显式处理（并假设 o.subscribed_date 是 DATE 数据类型）：

DECLARE
  CURSOR c_orgs    
  IS
     SELECT o.contact_id,      
            o.completed_date,
            o.submitted_date 
       FROM orders o 
      WHERE o.submitted_date BETWEEN TO_DATE(SubmittedDateFrom, 'yyyy-mm-dd') 
                                 AND TO_DATE(SubmittedDateTo, 'yyyy-mm-dd'); 
BEGIN
   FOR c_o IN c_orgs 
   LOOP
      DBMS_OUTPUT.put_line('Submitted date = '||c_o.submitted_date);
   END LOOP;
END; 

这确保任何隐式转换中没有错误，并且所有转换在其数据类型中都是显而易见的。

“问题是：我们无法找到任何信息明确表明 Oracle 9i 优化器在这种日期算术方面存在问题。这是这里发生的情况还是还有其他问题？”

我不认为它是优化器，它可能是隐式转换的最终产品导致了性能问题。由于我们没有 Oracle 数据库中的日期等 NLS 设置，因此很难判断，但如果使用显式转换可以提高性能，那么我建议您使用它们（而且也是更好的练习）。

希望它有帮助，奥利。

Answer 2

根据 Oracle 文档（这是 v10 ，但我想这也适用于 9i），“...不支持执行日期绑定变量隐式类型转换的语句的 EXPLAIN PLAN。”

除了 Ollie 建议的方法之外，您是否尝试过使用 trunc() resp。之间而不是？

SELECT
    o.contact_id, 
    o.completed_date, 
    o.submitted_date
FROM
    orders o /* 860,000 row table */
WHERE   
    trunc(o.submitted_date) = trunc(?)

分别。

SELECT
    o.contact_id, 
    o.completed_date, 
    o.submitted_date
FROM
    orders o /* 860,000 row table */
WHERE   
    o.submitted_date between ? and ? + 1