Re: Indexes, performance and storage

Jonathan,

Please accept my deepest apologies...it was my typing that caused your confusion..
The number of rows should read 20.3 million, not 2.3 million..

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:1007119437.18569.0.nnrp-10.9e984b29_at_news.demon.co.uk...
>
> 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
> >
>
>
Received on Fri Nov 30 2001 - 07:31:09 CST