Q

Function index for column with 80% unique records

I am having problems indexing a column where 80% of the 65,000 records are unique. Four times a day we get a data feed and insert, update and delete 2,000 records from the table. The particular column is a VARCHAR2(21). I have a query that takes 10 seconds to return a value and I have a bitmap index which doesn't seem to help. I have looked at function indexes but cannot figure out one that would work.

I am having problems indexing a column where 80% of the 65,000 records are unique. Four times a day we get a data feed and insert, update and delete 2,000 records from the table. The particular column is a VARCHAR2(21). My specific question is how to index the following query:
select * from table
where column like '%value%'
It takes 10 seconds to return a value and I have a bitmap index which doesn't seem to help. I have looked at function indexes but cannot figure out one that would work. Any suggestions would be appreciated.
Have you tried a function-based index using INSTR? It returns an integer indicating the position of the desired substring within the searched string; the return value is 0 if the string is not found. Then your query could be changed to:
select * from table
where instr(column, 'value') > 0;
This was last published in March 2006

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close