Re: Indexes, performance and storage

There is something a little unusual about your data - you have 2.3 million rows, but 2.5 GB of data, which indicates 1K per row, and the b-tree index you create takes
432MB which suggests the average length
of the identifier (when used) is about 155 bytes. Is your figure for the number of rows correct ?

It is possible for there to be a huge difference between b-tree size and bitmap size. In the unlikely case that your identifier was constant across the table, the bitmap would require 2,300,000/8 bytes, plus about 12% overhead for a total of about 320K - so your 8M, in an absolute worst case scenario, would be good for at least 25 distinct values for the identifier.

It sounds as if your sample column isn't a very good one to use a b-tree on, though, and if this is typical of the other columns you want to index you may NEED to get EE for the bitmaps.

You may get some benefit from compressing indexes, by the way - but I'm not sure that you can do that in SE either.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

Steve Bell wrote in message <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

>