Oracle 9i - Optimizer Issues with Date Arithmetic?

Question

We have a query that includes (but has other joins, tables and where conditions):

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 from a Java application.

The ? paramaters allow checking for orders submitted between two dates.

However, this query runs very slowly.

We converted to run from PL/SQL for testing as follows:

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:

1. convert the SubmittedDateTo value to to_date('2011-07-16', 'yyyy-mm-dd') (i.e. do the arithmetic outside the query),
2. 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.

"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?"

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).

Hope it helps, Ollie.