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 -> Re: Indexes w/8i Integer or bitmap char etc.?

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

From: <michael_bialik_at_my-deja.com>
Date: Thu, 25 Nov 1999 21:49:40 GMT
Message-ID: <81katj$79q$1@nnrp1.deja.com>


Hi.

 The usual answer "It depends...".

 I prefer to keep numeric data as NUMBER data type :

  1. It uses less space ( in data and index )
  2. 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 - 15:49:40 CST

Original text of this message

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