Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.oracle -> 'DD-MON-YY' v. 'DD-MON-YYYY' question

'DD-MON-YY' v. 'DD-MON-YYYY' question

From: DBF <nibble_at_bitman.org>
Date: Thu, 11 Oct 2007 20:10:06 -0500
Message-ID: <ekhtg3lddu2k6i5upjssm8krrfas7l77kv@4ax.com>

I have a table in Oracle9i that has an index on a Date field. The NLS_DATE_FORMAT is set to 'DD-MON-YY'. If I run a query on the table and compare the date field as >= '01-OCT-07' the execution plan uses the index and the query returns in < 1 seconds.

However if I use '01-OCT-2007' for the comparision to the date field. It does a full table scan and returns in 8 seconds.

In addition, if I use the Oracle to_date('01-OCT-2007') it also does a full table scan and returns in 8 seconds.

Examples:

select *
from mytable
where mydate >= '01-OCT-07'

results in Index usage on mydate and returns in < 1 seconds

select *
from mytable
where mydate >= '01-OCT-2007'

results in full table scan and returns in 8 seconds

select *
from mytable
where mydate >= to_date('01-OCT-2007')

results in full table scan and returns in 8 seconds

The table was just fully analyzed last night in an attempt to fix this problem, to no avail.

Any ideas why this is happening?
What should I look for in my Oracle settings?

thank you for any help or suggestions. Received on Thu Oct 11 2007 - 20:10:06 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US