RFE: Table sizes in INFORMATION_SCHEMA.TABLES

[GoogleCodeExporter]

It would be useful to have some additional columns in INFORMATION_SCHEMA.TABLES:
 * ROW_COUNT
 * DATA_SIZE
 * MAX_DATA_SIZE

Obviously I can do row counts manually, but it seems appropriate to have this 
info reported in the INFORMATION_SCHEMA.

There does not appear to be a way to determine table size.  This would handy to 
know especially for TEXT tables (2GB max?).  And of course knowing the maximum 
size allowed per-table would be useful too.

Original issue reported on code.google.com by James.Mo...@gmail.com on 17 Dec 2010 at 4:42

[GoogleCodeExporter]

There is no 2GB limit or the maximum size allowed per-table. I'm not sure what 
you mean with TEXT tables (do you mean HSQLDB?)

Original comment by thomas.t...@gmail.com on 21 Dec 2010 at 9:18

[GoogleCodeExporter]

Hmmm...  I guess I got confused and recalled incorrectly.  I have reviewed the 
HSQLDB project and its docs too so that is probably where the confusion comes 
from.  Sorry.  :)

Having said that, it would still be useful to have the ROW_COUNT and 
TABLE_SIZE/DATA_SIZE info available via the INFORMATION_SCHEMA, if possible.

Original comment by James.Mo...@gmail.com on 21 Dec 2010 at 9:37

[GoogleCodeExporter]

Can table size be calculated or approximated by pageSize * 
(allocatedTablePageCount - freeTablePageCount)?

Derby does something like that, I think and exports page size and counts in its 
information schema.

For row count I can loop on table names with SELECT COUNT(*)..., but it would 
take less time (and query processing) for H2 to report this in the 
information_schema.

Is this a reasonable request?

Original comment by James.Mo...@gmail.com on 29 Dec 2010 at 7:37

[GoogleCodeExporter]

Hi,

The row count is relatively easy to add. Calculating the data size and so on 
would take too long.

Why do you need this information?

Original comment by thomas.t...@gmail.com on 29 Dec 2010 at 7:55

[GoogleCodeExporter]

Its not really a need.  Its a want.  I like to monitor table data size because 
row count is not a valid indicator of data size and I can't use the filesystem 
to do that with the single-file format.

I might have a table with 5000 rows of 3 INTs that is smaller than another 
table with 50 rows of CLOBs.  But I may have several CLOB tables and I like to 
see the relative size of them.

I understand that the calculation would negatively impact the 
information_schema.  Would it be possible to instead expose a callable function 
to perform the calculation?

e.g. @sizeof public.mytable

Original comment by James.Mo...@gmail.com on 30 Dec 2010 at 1:14

[GoogleCodeExporter]

I would like to add a feature request in the roadmap, but so far I don't 
understand the use case: why exactly do you want to monitor the table data size?

LOB data is stored in separates tables since version 1.3:

create table t(d clob);
select * from information_schema.lobs;
select * from information_schema.lob_map;
select * from information_schema.lob_data;

Some LOBs might be used by multiple rows or even tables. You could query those 
tables to calculate the total data size used by a table. The tables are 
currently not documented (mainly because it's an internal mechanism, but also 
because 1.3 is still beta), but it should be fairly easy to understand how it 
works.

Original comment by thomas.t...@gmail.com on 15 Jan 2011 at 11:39

[GoogleCodeExporter]

To the end-user of a SQL database - its a black box.  You put data in and hope 
to get it out again.  Having some metric of data size inserted in the database 
is useful.  Row counts are one metric, although not really an indicator of data 
size/footprint.  For the DBs that store a table as a file on the filesystem, 
this information is available to the user without special DB functions.  For 
DBs that store all tables within a single file, this information is not 
available without some special DB function.

I'll take a peek at the table code to see if I can figure it out.

Original comment by James.Mo...@gmail.com on 19 Jan 2011 at 3:33

[GoogleCodeExporter]

It looks like class org.h2.index.PageDataIndex is the place to start.
It would require replicating chunks of the rowCount logic down through PageData 
to PageDataLeaf and PageDataNode.

Original comment by noelgrandin on 7 Mar 2011 at 12:38

[GoogleCodeExporter]

The easiest way is to use Table.getRowCount() or 
Table.getRowCountApproximation().

Original comment by thomas.t...@gmail.com on 11 Mar 2011 at 12:33

[GoogleCodeExporter]

What about PageData.getMemory() ?
memoryEstimated / memoryPerPage

Doesn't that estimate the number of bytes a page consumes?  That seems like 75% 
of the battle...

Original comment by James.Mo...@gmail.com on 11 Mar 2011 at 3:30

[GoogleCodeExporter]

memoryEstimated and memoryPerPage is the estimated heap memory (per row or per 
page), it's not the estimated size per table. I will document this. Heap and 
disk usage can be a lot different.

Original comment by thomas.t...@gmail.com on 11 Mar 2011 at 5:43

[GoogleCodeExporter]

I read an article on Javalobby this weekend that was directly related to this 
issue.  Thought I would link to it because it explains some good reasons to 
have this kind of functionality.

http://java.dzone.com/articles/calculating-sql-table-and-row

Original comment by James.Mo...@gmail.com on 4 Jul 2011 at 10:29

[GoogleCodeExporter]

This issue was closed by revision r4558.

Original comment by noelgrandin on 13 Dec 2012 at 10:13

• Changed state: Fixed

[GoogleCodeExporter]

Nice!  I'm looking forward to the next release with your changes!

Original comment by James.Mo...@gmail.com on 13 Dec 2012 at 3:59

[GoogleCodeExporter]

Hi

is there a way to get the data size of a table in HSQL?

since this discussion is very old ..i hope now there is a way..

tx in advance

Original comment by pavan29...@gmail.com on 4 Mar 2014 at 2:01

[GoogleCodeExporter]

> data size of a table in HSQL

Why do you need this feature? Would "select count(*) from tableName" work for 
you, and if not, why not?

Original comment by thomas.t...@gmail.com on 6 Apr 2014 at 1:31

[GoogleCodeExporter]

Hi

 i need this feature to know the memory size of the table because for my application there are number of records being inserted every hour and to know the data size occupied in disk so that i can increase my hardware..count(*) gives me the no. of records but i need data size in MB or GB.

Thanks for ur reply

Original comment by pavan29...@gmail.com on 7 Apr 2014 at 4:18

[GoogleCodeExporter]

So knowing the size of the file would be enough for your?

Original comment by thomas.t...@gmail.com on 7 Apr 2014 at 4:23

[GoogleCodeExporter]

yes..

Original comment by pavan29...@gmail.com on 7 Apr 2014 at 4:27

[GoogleCodeExporter]

OK, you can get the file size using:

call 
(SELECT cast(value as bigint) FROM INFORMATION_SCHEMA.SETTINGS 
where name = 'info.PAGE_COUNT') * 
(SELECT cast(value as bigint) FROM INFORMATION_SCHEMA.SETTINGS 
where name = 'info.PAGE_SIZE')

Original comment by thomas.t...@gmail.com on 10 Apr 2014 at 4:14

[GoogleCodeExporter]

Hi

when I am trying to execute the query I am getting the following error 

user lacks privileges or object not found
i am using the credentials SA as username and no password

Thanks a lot for your time and response

Thanks

Original comment by pavan29...@gmail.com on 10 Apr 2014 at 7:23

[GoogleCodeExporter]

That doesn't sound like a H2 error message.

Original comment by thomas.t...@gmail.com on 10 Apr 2014 at 7:38