Determining table size with table conditions

Tools

Determining table size with table conditions

Hi All!

I was wondering if there is a way to determine table size of part of a table. For example, I have a table with 5 years worth of data but am interested in seeing how much space 1 year of that data takes up. Delta columns being version_start_dt.

I thought about creating temp tables and then determining the size of those but I have 13 tables to do this for so I was wondering if there was something I was unaware of that would be faster.

Thanks!

1 REPLY
Senior Apprentice

Re: Determining table size with table conditions

This approach should get you an approximate size without copying data:

SELECT yr, currentperm * prct
FROM
(
SELECT
SUM(currentperm) AS currentperm
FROM DBC.TableSizeV
WHERE DatabaseName = ...
AND TableName = ...
) AS dt1,
(
SELECT EXTRACT(YEAR FROM version_start_dt) AS yr, COUNT(*) AS cnt, CAST(cnt AS DEC(18,6)) / SUM(cnt) OVER () AS prct
FROM ...
GROUP BY 1
) AS dt2

Dieter