Ask the Expert

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?

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: