Re: HELP !! Function based index

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 Received on Thu Mar 08 2001 - 12:38:38 CST