How can I work out the maximum row size in a table, if I'm only given the datatype lengths (from all_tab_cols.data_length column) of the columns in the table (ie no statistics or ANALYZE)? There's extra complications in that this is an IOT, so there's index tree size to consider as well.
views:
83answers:
2
A:
The formulas sizing indexes are involved -- they're generally focused on calculating rows per block to determine actual disk space required for N rows -- and the last time I can find that Oracle included this in their documentation was in Oracle 8.0. See here.
Adam Musch
2010-03-25 14:22:53
A:
I'd recreate the table, generate some data with a Data Generator and actually measure it. I generally wouldn't go further than 10s of MB of generated data as sufficient for a guide.
In some places, they tend to generate over-generous data length (eg standardizing on string sizes of 20, 50, 100 bytes/characters). Even on names you may allow 30 characters when most people are in the 5-10 length. As such estimates derived from field sizes, rather than actual lengths, will be VERY vague and you'll have a very large error margin.
Gary
2010-03-25 22:43:20
I can't add or change anything in the database; it has to be a formula I can run on the client machine only.
thecoop
2010-03-26 12:12:02