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

Hi Chris,

 the better wording for bitmap indexes would be:  might not work well for a large number of distinct values.

 large is to be seen relative to the total number of records.

Karsten

In article <s3rmvomia4r65_at_corp.supernews.com>,   "Chris Means" <cmeans_at_holdthespam.empowergeo.com> wrote:
> 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.
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Nov 26 1999 - 06:17:10 CST