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

Michael,

Thanks.

Yes, it depends...but based upon what you said it looks like having four seperate indexes is the way to go.

Statistically:

56917 distinct Code1's,
75834 distinct Code2's,
with a total of about 670,000 records.

Just playing around with creating various indexes, types, and combinations, it certainly seems like having 4 seperate indexes works well. I'm currently using bitmap indexes...but you mentioned that bitmap indexes might not work well for a large number of records...what do you mean by large?

Thanks for your input.

I'll also change the code fields to Numbers...

<michael_bialik_at_my-deja.com> wrote in message news:81katj$79q$1_at_nnrp1.deja.com...
> Hi.
>
> The usual answer "It depends...".
>
> I prefer to keep numeric data as NUMBER data type :
> a. It uses less space ( in data and index )
> b. It prevents some programmer from stuffing 'ABCDEF' string
> into the CUSTOMER_ID field.
>
> 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 ).
> --
> END IF;
> ELSE
> Define an index for each of fields ( 4 indexes ).
> If <CODE1 & CODE2 fields have a limited number of values> THEN
> Create a BITMAP index for them
> ELSE
> Create regular b-tree index
> END IF
> END IF;
>
> HTH. Michael.
>
> 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 - 19:00:53 CST