Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Indexes w/8i Integer or bitmap char etc.?
Hi.
The usual answer "It depends...".
I prefer to keep numeric data as NUMBER data type :
Are your queries is of 2 type you mentioned only
CODE1 is always used with CODEWORD2 and vice versa ?
IF yes THEN
IF <IN list is short> THEN
define 2 indexes ( CODE1, CODEWORD2 ) and ( CODE2, CODEWORD1 ). -- In that case optimizer will rewrite queery as an UNION ELSE define 2 indexes ( CODEWORD2, CODE1 ) and ( CODEWORD1,CODE2 ). --
In article <s3ocdr46rrp87_at_corp.supernews.com>,
"Chris Means" <cmeans_at_holdthespam.empowergeo.com> wrote:
> 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
>
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Thu Nov 25 1999 - 15:49:40 CST