Re: Indexes on date fields

Hi Dipen,

This depends whether you are using rule based or cost based optimizer. CBO: the only thing that counts is the amount of IO needed. RBO:
case 1: the index will be used
case 2: the index will be used
Note: don't write this as date_field >= date_variable1 and date_field1 <= date_variable2
if more ANDs are involved. The optimizer might easily combine wrong and clauses.
Always write this as between.
General note: Make sure there is either no time in your date field, or in all your where clauses take into account there is a date in your field. This results in
between ... and to_date(date_variable2) + 1 - 1/3600

Hth,

Sybrand Bakker, Oracle DBA

<dipenk_at_my-deja.com> wrote in message news:7muodq$n1v$1_at_nnrp1.deja.com...
> We have a large(ish) table with approx 1m records and quite a few date
> fields, and these date fields are used regularly to select records. Is
> it worth putting indexes on these date fields? Would the indexes be used
> in queries such as
>
> e.g.1 :
>
> select *
> from large_table
> where date_field1 <= date_variable
>
> e.g. 2 :
>
> select *
> from large_table
> where date_field2 BETWEEN date_variable1 AND date_variable2
>
> Thanks in advance for any help.
>
> Regards,
>
> Dipen
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Mon Jul 19 1999 - 04:59:05 CDT