Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: HELP !! Function based index

Re: HELP !! Function based index

From: Spencer <spencerp_at_swbell.net>
Date: Wed, 7 Mar 2001 20:55:36 -0600
Message-ID: <pDCp6.95$J26.106687@nnrp3.sbc.net>

A couple of possibilities: optimizer is set to RULE instead of CHOOSE, statistics are not collected on either table, or Oracle is estimating a lower cost for a full tablescan than access via an index.

I would suggest that you either:

 collect statistics on both tables, e.g. ANALYZE TABLE stg_session_detail_t2
COMPUTE STATISTICS FOR ALL INDEXES; and/or include a hint in the query, e.g. SELECT /*+ FIRST_ROWS */ ... "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 Wed Mar 07 2001 - 20:55:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US