Re: HELP !! Function based index

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
Received on Thu Mar 08 2001 - 03:16:11 CST