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
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in April 2003