oracle function based index not being utilized 
Author Message
 oracle function based index not being utilized

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



Wed, 19 Jan 2005 03:40:12 GMT
 oracle function based index not being utilized


Quote:
>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



Wed, 19 Jan 2005 03:55:12 GMT
 oracle function based index not being utilized
How many records in consumer?

Quote:

>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

-----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
   http://www.newsfeed.com       The #1 Newsgroup Service in the World!
-----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----


Wed, 19 Jan 2005 03:54:42 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Feature not enabled: Function-based Indexes

2. SQL Server 2000 does not utilize the most efficient index

3. Function Based Index in Oracle 8

4. Error while Creating function based index in Oracle 8

5. Oracle 8i and function-based indexes

6. Function-based indexes in Oracle 8i

7. Oracle user defined Function based index and Ora-4020

8. Function based indexes and index fast full scan

9. ORA-01031 with Create Index for a Function Based Index

10. Select not returning records based on Date I am using

11. Function Based indexes in SQL Server


 
Powered by phpBB® Forum Software