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?

Marcus

Page 1 / 1

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

Hi Deepak,

We needed also the record count in the tables, so I came up with the following query which seems to be working for us in IBC:

SELECT

    PS.PROJECTION_SCHEMA,

    PS.ANCHOR_TABLE_NAME AS "TABLE_NAME",

    (SUM(SC.TOTAL_ROW_COUNT) - SUM(SC.DELETED_ROW_COUNT)) / COUNT(DISTINCT SC.PROJECTION_ID) AS "ROW_COUNT"

FROM PROJECTION_STORAGE AS PS

    JOIN STORAGE_CONTAINERS AS SC ON 1=1

        AND PS.PROJECTION_SCHEMA = SC.SCHEMA_NAME

        AND PS.PROJECTION_ID = SC.PROJECTION_ID

        AND PS.NODE_NAME = SC.NODE_NAME

GROUP BY PS.PROJECTION_SCHEMA, PS.ANCHOR_TABLE_NAME

Kind regards,

Adam

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN