473,473 Members | 2,178 Online
Bytes | Software Development & Data Engineering Community
Create Post

Home Posts Topics Members FAQ

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

DBF

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.
Oct 12 '07 #1
0 1860

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Stevey | last post by:
I have the following XML file... <?xml version="1.0"?> <animals> <animal> <name>Tiger</name> <questions> <question index="0">true</question> <question index="1">true</question> </questions>
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
53
by: Jeff | last post by:
In the function below, can size ever be 0 (zero)? char *clc_strdup(const char * CLC_RESTRICT s) { size_t size; char *p; clc_assert_not_null(clc_strdup, s); size = strlen(s) + 1;
56
by: spibou | last post by:
In the statement "a *= expression" is expression assumed to be parenthesized ? For example if I write "a *= b+c" is this the same as "a = a * (b+c)" or "a = a * b+c" ?
2
by: Allan Ebdrup | last post by:
Hi, I'm trying to render a Matrix question in my ASP.Net 2.0 page, A matrix question is a question where you have several options that can all be rated according to several possible ratings (from...
3
by: Zhang Weiwu | last post by:
Hello! I wrote this: ..required-question p:after { content: "*"; } Corresponding HTML: <div class="required-question"><p>Question Text</p><input /></div> <div...
1
by: love91501 | last post by:
I was wondering if anyone would be able to help me figure out how to make my Q010 have the ability to chose 2 answers, I'm hoping to stay away from checkboxes but I am willing to take in any advice....
6
semanticnotion
by: semanticnotion | last post by:
Hi sir i want to transform the data of one table into another through foreign key but the following error come to my browser Here is my code and data base structure. CREATE TABLE IF NOT...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
1
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
1
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...
0
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...
0
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.