Re: date column in where clause -> suppressing index?

If you have Oracle8i you can create an index based on a function: create index my_index on my_table (trunc(my_date,'DD'));

With the previous release, you can add a column to your table containing only the date and index it:
alter table my_table add (new_date date); create index my_index on t(new_date);

You can add a trigger to fill this column: create trigger my_trigger before insert or update on my_table for each row
begin

   :new.new_date := trunc(:new.my_date,'DD'); end;
/

And then search on this column...

--
Have a nice day
Michel


Jerra <jerra_at_aland.net> a écrit dans le message : 8lm2g9$4uv$1_at_gandalf.alcom.aland.fi...


> When using date and time how do I search for specific dates most efficently?

> Using where trunc(date,'DD') = to_date('26-07-2000','MM-DD-YYYY') the index

> for the date column can't be used.

> The time portion is important and can't be 00:00:00 and there is a lot of

> searches for posts for a specific day.

>

> TIA!

>

> /Jerra

>

>