db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: table size estimation
Date Fri, 03 Nov 2006 18:22:13 GMT
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?


Mime
View raw message