Number of records per table (IBC Vertica)

Hello Celonis community,

I want to determine the number of data records in vertica for all tables of a schema (SAP System).

The easiest way should be via the system table “tables”

so I get all tables of a SAP connection, which also works
select * from tables where table_schema = ‘XYZ’

but unfortunately the following does not work:
select t.table_name,
(select count(1) from t.table_name) as table_row_count
from tables as t where t.table_schema = 'XYZ

-> Execution error: Not allowed to access schema t

the other way via a system table does not work reliably:
SELECT table_schema,
table_name,
SUM(row_count) AS row_count
FROM (SELECT table_schema,table_name,
NVL(CASE
WHEN NOT is_segmented THEN (row_count / node_cnt)::INT
ELSE row_count
END, 0) row_count
FROM (SELECT t.table_schema,
t.table_name,
ps.projection_name,
(SELECT MIN(is_segmented)
FROM projections p
WHERE p.projection_name = ps.projection_name
AND p.projection_schema = ps.anchor_table_schema) is_segmented,
node_cnt,
SUM(ps.row_count - NVL(dv.deleted_row_count, 0)) row_count
FROM tables t
LEFT JOIN projection_storage ps
ON t.table_id = ps.anchor_table_id
LEFT JOIN delete_vectors dv
ON dv.node_name = ps.node_name
AND dv.schema_name = ps.anchor_table_schema
AND dv.projection_name = ps.projection_name
CROSS JOIN (SELECT COUNT(1) node_cnt FROM nodes WHERE node_type = ‘PERMANENT’) n
where
t.table_schema = 'XYZ’
GROUP BY 1, 2, 3, 4, 5
LIMIT 1 OVER(PARTITION BY t.table_schema, t.table_name ORDER BY 1)) foo) foo2
GROUP BY table_schema,table_name
ORDER BY table_schema,table_name

Anybody got any ideas?
Br
Marcus

Hi Marcus,

I am afraid that the only way to retrieve the number of records for a table is a simple
SELECT COUNT(1) FROM TABLE_NAME

There is no easy way to retrieve this information for all tables in a schema.

Best regards,
Leonid

Hello Leonid,
we wanted to get the list of all tha tables and their record count and storage size. I used something like below but the output is not matching with original count and sizes given in data consumption in data pool :

SELECT anchor_table_schema,
anchor_table_name,
SUM(used_bytes),
SUM(row_count)
FROM v_monitor.projection_storage
GROUP BY anchor_table_schema, anchor_table_name

Do you have something better idea to get list of all the tables with thier count and storage size?

Thanks in the advinace!

Best Regards,
Deepak

Hi Deepak,

Unfortunately, the only good way to retrieve the number of records is a simple
SELECT COUNT(1) FROM TABLE_NAME. If you interested in the size of a single table, the data consumption view is the best place to see it.
We measure raw data stored though which does not match the value that you obtain from the projection_storage table. The usage of this table is therefore not recommended.

Best,
Leonid