Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Indexes w/8i Integer or bitmap char etc.?

Indexes w/8i Integer or bitmap char etc.?

From: Chris Means <cmeans_at_holdthespam.empowergeo.com>
Date: Wed, 24 Nov 1999 12:42:52 -0600
Message-ID: <s3ocdr46rrp87@corp.supernews.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US