I have no idea if this applies, but one problem with using this method
is that the space table will report space that is allocated to deleted
rows. reclamation of deleted rows is deferred so if you have deleted
rows, or aborted inserts, or attempted to insert rows but failed due
to duplicate key violation then the table may be using space to store
deleted rows.
Also by default tables reserve empty space in the page to allow for the
rows to expand during future updates:
http://db.apache.org/derby/docs/dev/tuning/rtunproper28026.html
To see the overheads for per row and per field see, note page size
affects overhead size so you will need to know page size, don't forget
to account for page header and offset table overhead:
http://db.apache.org/derby/papers/pageformats.html#records
Also the last page in the table is not going to be filled so if you
don't have a lot of rows in the table this may dominate the calculation.
So worst case would be 1 row in a 2k page would give ~2k row size where
only ~100 bytes actually used.
timestamp is stored a 3 int's - so 12 bytes for the data portion
(date, time, and nanoseconds).
S wrote:
This is a follow up email to my earlier posting regarding storage. Based
on the query given,
select * from NEW org.apache.derby.diag.SpaceTable('SYS','SYSTABLES') AS x;
using it for my table, the estimated size of a record is coming to be
131 bytes. The table consists of 5 bigints, 3 smallints, 1 timestamp and
a varchar. The avg length of the varchar is 14chars. So, total size of
the record size should have been 5*8+3*2+14+(bytes for timestamp)+any
additional per field/record data all put together shouldn't be more than
70/80 bytes. So, why am I getting 131 bytes?
