Indexes, performance and storage

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 Thu Nov 29 2001 - 19:01:59 CST