Re: HELP !! Function based index

From: Iain Knowles <i.knowles_at_ntlworld.com>
Date: Fri, 9 Mar 2001 22:45:04 -0000
Message-ID: <t_cq6.3707$m56.34940@news11-gui.server.ntli.net>

The user obviously has the QUERY REWRITE system privilege to be able to create the function based index, but it will not be used unless the init.ora paramter

QUERY_REWRITE_ENABLED=TRUE is set

alternatively

ALTER SESSION SET query_rewrite_enabled=true;

Iain

"Dan White" <dwhite_at_icimail.com> wrote in message news:986fj50q6s_at_drn.newsguy.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
>
Received on Fri Mar 09 2001 - 16:45:04 CST