Measuring table size

{question}

How do we measure the table size for a particular DB schema?

{question}

 

{answer}

Note! It applies only for tables with no partitioning enabled.

To calculate the size of a table, the 'catalog id' needs to be extracted for the table according to the schema. In the DB archive directory, the name of the directory containing the table data is matching the 'catalog id' (Each table is uniquely identified by a pair of 32-bit integers, a unique ID within the catalog, and the catalog ID). 

  • Example: (NuoDB 4.0.2-4)
//get table name and catalog id this way: catalog id = object id +1
select (objectid+1) FROM system.tables WHERE SCHEMA='<schema name>'
AND
object_id IS NOT NULL; 
SQL> SELECT SCHEMA, 
tablename,
( objectid + 1 ) AS catid
FROM system.tables
WHERE SCHEMA = 'TEST'
AND objectid IS NOT NULL;

 SCHEMA  TABLENAME  CATID 
 ------- ---------- ------

  TEST      T1        95 
  TEST      TEN      97 

In the archive directory, each table data is stored in a separate directory with a directory name = catalog id:

du -hc --max-depth=0 95.cat 97.cat
1,2G    95.cat
16K    97.cat
1,2G    total

 In this example, the table T1 has catalog id=95, and the size on the disk is 1.2 GB.

You may find more information about this topic here.

If you have any questions, please reach out by clicking here

{answer}

Have more questions? Submit a request

Comments