Question

我们有一个问题,包括(但还有其他加入、表格和条件):

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

called诉。

<代码>? paramaters允许核对两个日期之间的订单。

然而,这种询问进展缓慢。

我们从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;

IF we either:

convert the SubmittedDateTo value to to_date( 2011-07-16 , yyyy-mm-dd ) (i.e. do the arithmetic outside the query),
make SubmittedDateTo a string and use "to_date( SubmittedDateTo , yyyy-mm-dd ) + 1" as the second condition in the WHERE.

THEN, the query speeds up dramatically ( < 1 second vs. 44+ seconds).

Further information:

running an explain plan on the query gives the error ORA-00932: inconsistent datatypes: expected DATE got NUMBER
the submitted_date column has an index and stats, etc. have been run
wrapping the SubmittedDateTo + 1 in a trunc() call does not affect performance
We do not have a non-9i database with similar data volumes, etc. to test if it s the version of Oracle or not.

The question is: we are unable to find any information which clearly states that the Oracle 9i Optimizer has an issue with this kind of date arithmetic. Is that what s happening here or is there something else going on?

Answer 1

I d always ensure that all conversions are handled explicitly (and assuming o.submitted_date is a DATE datatype):

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;

This ensures there is no errors in any implicit conversions and all conversions are obvious in their data type.

“问题是:我们无法找到任何明确表明Oracle 9i Bestizer有这种计算日期的问题的信息。这里发生的情况,还是还有什么事情发生?

I don t think it is the optimiser, it could be the end product of your implicit conversions are causing the performance issues. As we do not have you NLS settings for dates etc from the Oracle database it d be hard to tell but if using explicit conversions increases performance, then I d suggest you use them (and it s better pracitce too).

希望会帮助Olie。

Answer 2

http://download.oracle.com/docs/cd/B13789_01/server.101/b10752/ex_plan.htm 甲型六氯环己烷 (第10条),但我猜测这也适用于9i,“......不支持EXPLAIN PLAN, 用于进行日期约束变数的默示类型转换的声明”。

Apart from the approach sugggested by Ollie, have you tried using trunc() resp. between instead?

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

resp.

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

友情链接