Re: Date Data Type in Index

From: <michael_bialik_at_my-deja.com>
Date: Sat, 09 Oct 1999 11:55:41 GMT
Message-ID: <7tnafr$8nm$1@nnrp1.deja.com>

Hi.

 The only thing I can think about is that Oracle DATE  is actually DATE & TIME format.
 It means that if you have something like that :

 CREATE TABLE tab_a (

   entry_date        DATE,
   comment           VARCHAR2 ( 30));

 CREATE INDEX tab_a$entry_date on tab_a ( entry_date );

 Insert some data.

 If you write
  SELECT * FROM tab_a
 WHERE entry_date = TO_DATE('09101999','DDMMYYYY');  No rows will be retrieved.
 If you write :
  SELECT * FROM tab_a
  WHERE TRUNCATE(entry_date) = TO_DATE('09101999','DDMMYYYY');  The index will NOT be used.
 ( I think that the "problem" you are referring to ).  You have either to truncate time portion on INSERT/UPDATE  or
  SELECT * FROM tab_a
  WHERE entry_date = BETWEEN
   TO_DATE('09101999000001','DDMMYYYYhh24miss') AND    TO_DATE('09101999235959','DDMMYYYYhh24miss') ;

 HTH. Michael.

 P.S. I prefer truncating the input data, but ...

In article <7tmbpv$ul2$1_at_nntp4.atl.mindspring.net>,   "Charlie Briney" <cbriney_at_mindspring.com> wrote:
> I have been told there is a problem using a column which is a date
data type
> in an index. The person telling me this did not know what that
problem is.
> Does anyone know what problem can be caused by this? If there is one,
is it
> version dependent? Is there a work around for it?
>
> Thanks,
> Charlie
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Oct 09 1999 - 06:55:41 CDT