Q

Indexing a 15-column table for performance

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

 colkey1	NUMBER(15) PRIMARY KEY
 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


This was first published in April 2003

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close