Re: HELP !! Function based index

Dan White wrote:
>
> Deana,
>
> Read your 8i documentation "Function based Indexes"
>
> Dan
>
> In article <3AA74DDB.70B96A92_at_ina.hr>, deana says...
> >
> >if you use any function on column that is index oracle performs full scan.
> >
> >why do you use to_char on index column ?
> >
> >anyway, write select ... where to_char(a.date_dt,'dd.mm.yyyy') = b.d_timestamp
> >
> >Dan White wrote:
> >
> >> 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
> >
>
> Dan White
> programmer/analyst

Maybe this will help...

http://www.oracledba.co.uk/tips/function_indexes.htm

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"