Re: Index-Organized tables and Space Usage

knut_meidal_at_email.com (Knut E. Meidal) wrote in message news:<64c6c1ea.0303201826.548e1bb2_at_posting.google.com>...
> I recently got the Kyte book, and read up on tables.
> Index-organized tables caught my eye, and especially the suggestion
> that they may save some space on the server. "Excellent", I thought,
> "lemme see how this works."

thanks for providing an example iot against which to validate the formula which I proposed in my thread "size of an index-organised table".

I suspect that the 1 358 MB size for your original table does not include the space to store the primary key index since the average size of a row in your original table is about 100 bytes, while the "row size" of the table is 271 bytes.

making that assumption, and correcting my formula for rowperblk to - rowperblk = Int((blk - blkvhdsz)*bfill/rcd) I get 2 068 MB as the predicted size of your iot.

going back to your original table, adding space for a b-tree to store 14 000 000 primary keys of 39 bytes plus 8 byte block pointer would require at least 600 MB (without key compression), so the storage requirements of the two table organisations are similar.

access cost may be similar too since you're using an overflow data segment - assuming that you need access to the entire row. hmm, it would be interesting to see the size of everything if you didn't use an overflow data segment :-)

Eric Received on Fri Mar 21 2003 - 10:30:27 CST