Re: Indexes, performance and storage

Steve,

It is not possible to say that X MB of Table will require Y MB of index space.
From my experience, estimating index size is extremely dofficult.

What i would advise is this. Take some data (Say 25,000 Rows - 50,000) Rows from your table and put it into a temporary table. Create your indexes on this temporary table and determine its space. After that extrapolate it linearly.
You wll get reasonably good results unless the table is skewed badly.

Bitmap indexes are used for columns with low cardinality. The cardinality is determined by SELECT COUNT(DISTINCT(MyIndexColumn)) from MyTable

Hope this helps

Warm Regards
Mark

Steve Bell <stephen.bell_at_sympatico.ca> wrote in message news:<3C06DA87.704FB894_at_sympatico.ca>...
> Hi all,
>
> I have a table of about 2.3 million records (Oracle 8.17).
> The data takes approximately 2.5 GB..in our central database running on
> Enterprise edition we have a composite normal index (a two digit
> province identifier and last name) and a bitmap on another
> identifier..the bitmap takes about 8 MB..
>
> We are deploying this to 301 field offices that use Standard edition and
> I'm told we can't use bitmap indexes..when we create a normal index on
> the same field the size jumps to approx 423 MB..I knew bitmaps took less
> space but I just came to realize how much less!
>
> Anyway, ideally we would like to index some other columns in this table
> because it is used often for searches and there is never any DML
> performed on it while deployed..it is always query only.
>
> My question is, does a fifty fold increase in storage sound right
> between a bitmap and a normal index and secondly,
>
> Is it reasonable in your collective experience to anticipate that a 2.5
> GB table might require 1 GB or conceivably 2 GB of storage for indexes?
>
> I'd appreciate any thoughts on the tradeoff between storage and
> performance because storage could be an issue if we load our field
> servers with an extra 3.5 to 4 GB on top of what they already have.
>
> Best respect and regards,
>
> Steve
Received on Fri Nov 30 2001 - 00:55:55 CST