Xref: alice comp.databases.oracle.server:75633
Path: alice!news-feed.fnsi.net!news.maxwell.syr.edu!nntp2.deja.com!nnrp1.deja.com!not-for-mail
From: karsten_schmidt8891@my-deja.com
Newsgroups: comp.databases.oracle.server
Subject: Re: Indexes w/8i Integer or bitmap char etc.?
Date: Fri, 26 Nov 1999 12:17:10 GMT
Organization: Deja.com - Before you buy.
Lines: 151
Message-ID: <81lto4$7ud$1@nnrp1.deja.com>
References: <s3ocdr46rrp87@corp.supernews.com> <81katj$79q$1@nnrp1.deja.com> <s3rmvomia4r65@corp.supernews.com>
X-Article-Creation-Date: Fri Nov 26 12:17:10 1999 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 4.01; Windows NT)
X-Http-Proxy: 1.0 proxy.damp.de:8080 (Squid/2.0.PATCH2), 1.0 x35.deja.com:80 (Squid/1.1.22) for client unknown, 194.163.28.186
X-MyDeja-Info: XMYDJUIDkarsten_schmidt8891

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@corp.supernews.com>,
  "Chris Means" <cmeans@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@my-deja.com> wrote in message
> news:81katj$79q$1@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@corp.supernews.com>,
> >   "Chris Means" <cmeans@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.