Problem solve Get help with specific problems with your technologies, process and projects.

Indexing a 15-column table for performance

I've got a table with static data around 500,000 rows. This table has 15 columns:

 col1	VARCHAR2(50)
 col2	VARCHAR2(50)
 col3	VARCHAR2(50)
 col4	VARCHAR2(50)
 col5	VARCHAR2(50)
 col6	VARCHAR2(50)
 col13	VARCHAR2(50)
 col14	VARCHAR2(50)
I've also got a simple application where a user can select the columns (col1 till col14) to get his/her information on screen. There are no rules for the selection, so every combination is permitted. What is the best way to get a good performance for this table? I think there will be also performance problems when I create an index for every combination. Is maybe a bitmap index an option?

You are correct that you can over-index this type of application table. Unfortunately, bitmap indexes won't necessarily help. They might, but they might not help. So you will have to test for your sepecific environment. Where bitmap indexes may help the most would be in the fact that multiple column's indexes can be used to satisfy the query. Bitmap indexes cooperate with each other this way, where the standard B-tree index does not.

Another thing to consider is to cache the entire table in memory so that indexes don't need to be used. This isn't exactly a small table, but 500K rows isn't terribly large either. The KEEP buffer pool can be used to store the table so that it doesn't get aged out of the cache. This might be a solution for your case.

For More Information

Dig Deeper on Oracle database design and architecture

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.

Please create a username to comment.