Re: oracle function based index not being utilized

On 2 Aug 2002 12:40:12 -0700, hhjin_at_yahoo.com (Helen) wrote:

>We are running on Oracle EE 8.1.7.3 on Sun Solaris 5.8.
>We use cost based optimizer.
>
>I have created a function based index as of following,
>
>create index IDX_CONSUMER_LOWER_FIRSTNAME
>on consumer(lower(first_name));
>
>Then I need to run the following query, it suppose to utilize
>the index I have created, but it did not.
>
>select consumer.id from consumer
>where lower(first_name) = 'serpie';
>
>
>This query still does a full table scan,
>
>Query Plan
>-------------------------------------------
>SELECT STATEMENT Cost = 7465
>TABLE ACCESS FULL CONSUMER 1
>
>Any input?
>
>Thanks
>
>Helen

You have

- analyzed the table
- analyzed the index
- set query_rewrite_enable to true

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Aug 02 2002 - 14:55:12 CDT