How to calculate sizes of individual rows
Date: Thu, 21 May 2015 12:26:36 -0500 (CDT)
Message-ID: <e024ec16e1587c8632eaf1270d3a0a4f.squirrel_at_society.servebeer.com>
Hey all,
In 11.2.0.3, I'm looking into the storage of our largest table, which has 268 columns of which the largest is NCHAR(50) at 100 bytes (AL16UTF16). Recent stats collected peg the average row length at 2669 bytes. If I use the formula in MOS 1370050.1 of (roughly):
SELECT SUM(nvl(vsize(col_A),0)+nvl(vsize(col_B),0)+nvl(vsize(col_C),0)+ ...) FROM our_largest_table;
...I come up with:
Average: 2400.08 Minimum: 2326 Maximum: 2446
OK, so the 2400 average is nowhere near "2669" of the stats average, but it is suspiciously close to 2400+268, or adding a 1-byte length descriptor for each column. Empirically, this might be:
Row Length = SUM(NVL(VSIZE(Columns),0)+1 ...)
Of course, this doesn't necessarily match the documentation:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT1051
...although I think it's close. Does the average use a CEIL to bump it up
from 2668.08 to 2669? And where's the row header in the calc?
So, it appears to me that the MOS article isn't completely correct. Anyone know where I could find a more complete calculation?
Thanks,
Rich
p.s. Yes, I'm ignoring the potential >255 column issue for now, which lead me to look at this table's storage in the first place.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 21 2015 - 19:26:36 CEST