{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}
Comments