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

Home -> Community -> Usenet -> c.d.o.server -> R: Date Data Type in Index

R: Date Data Type in Index

From: Riccardo Ferrari <riccardo.ferrari_at_informatica2.it>
Date: Thu, 14 Oct 1999 18:52:38 +0200
Message-ID: <7u51uf$orm$1@nslave1.tin.it>


> If you write
> SELECT * FROM tab_a
> WHERE entry_date = TO_DATE('09101999','DDMMYYYY');
> No rows will be retrieved.

Why? It depends on the inserted value.If you had iussed an insert stmt such as
INSERT INTO tab_a (entry_date) VALUES
TO_DATE('09101999','DDMMYYYY');
then the query retrieves it.

In general the problem on date field retrieval is related to the meaning of the field itself. For example, if you meet conditions such as

-  you deal with date field with 'day' level precision
-  you store it in the DB with the 'time' part = 00:00:00
-  your queries are mostly concerned with day level comparisons
then an index on this field will be used and usefull. If, for example the second condition cannot be guaranteed, but the other two are still valid, you have to truncate the DB date to compare it with the result of no index use.

To summarize: it depends on your application!

Regards
Ricardo

<michael_bialik_at_my-deja.com> wrote in message 7tnafr$8nm$1_at_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.
>
Received on Thu Oct 14 1999 - 11:52:38 CDT

Original text of this message

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