oracle function based index not being utilized

From: Helen <hhjin_at_yahoo.com>
Date: 2 Aug 2002 12:40:12 -0700
Message-ID: <ebcac1e2.0208021140.4d0c18e6@posting.google.com>

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 Received on Fri Aug 02 2002 - 14:40:12 CDT