Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Indexes w/8i Integer or bitmap char etc.?
Hi,
I'm moving an existing application from single user (dbase & cdx) to Oracle 8i.
We're currently using bitmap indexes on the dbase stuff, and getting fanstatic result times going again 700,000+ records.
Obviously, I'd like to keep the result times (though I also realize going client/server is going to cause a performance hit).
I don't really understand the various performance improvements I can get with Oracle8i depending upon how the index is built. I've got a number of books on Oracle8i, but none of them are deep enough to tell me the differences.
Here's a peek at our table structure:
create mytable (
Code1 char(6), /* really just a numeric value */
Code2 char(6), /* really just a numeric value */
CodeWord1 varchar2 (30), /* one or more alphanumerics */
CodeWord2 varchar2 (30) /* one or more alphanumerics */
...other fields...
) ;
Our queries are:
select * from mytable where code1 in (...) and codeword2 like '...%' ; select * from mytable where code2 in (...) and codeword1 like '...%' ;
What would be the best types of indexes to use here?
Should I create indexes on each of the fields individually? or in some sort of combination (code1, codeword2) ? Would it be better to store Code1 & Code2 as Number(6) rather than as char(6) or varchar2(6)?
The data does not change (except during mainentance phases), would a bitmap index of somesort work best?
Disk space is NOT an issue, speed is of primary concern.
I know I can improve performance by placing the index files on differnent drives than the data, but for the moment, everythings' on one drive.
Any input would be greatly appreciated!
Thanks.
-Chris Means Received on Wed Nov 24 1999 - 12:42:52 CST