Re: HELP !! Function based index

"SaraN" <Vishmayaa_at_yahoo.nospam.com> wrote in message news:3AA7D23D.67C5AF5A_at_yahoo.nospam.com...
> Dan
>
> If you are not using COST based optimiser, the only way to get it going
> is to use HINTS.
>
> Been there.
> Hope this helps
>
> SaraN
> vishmayaa_at_yahoo.com
>
> > Hello all,
> >
> > I have a table in which I have a date column called d_timestamp. I
 have a
> > function based index on this column
> >
> > create index ix_sdt2_ts_fbi on stg_session_detail_t2
> > (to_char(d_timestamp,'DD-MON-YYYY'));
> >
> > I run the following query and get a full table scan
> > why?
> >
> > SELECT a.time_id, a.date_dt, b.timestamp
> > FROM cdm_time_dim a, stg_session_detail_t2 b
> > WHERE to_char(a.date_dt,'DD-MON-YYYY') =
 to_char(b.d_timestamp,'DD-MON-YYYY');
> >
> > I'm getting a fulltable scan on the stg_session_detail_t2 table.
> >
> > Thanks in advance
> > Dan
> >
> > Dan White
> > programmer/analyst
>
>

Have you re-analyzed the table/index? If not, have a DBA do this then try again (or look-up ANALYZE in the SQL reference).

Cheers,

Richard Gowan Received on Sat Mar 10 2001 - 16:00:03 CST