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.
This approach should get you an approximate size without copying data:
SELECT yr, currentperm * prct
SUM(currentperm) AS currentperm
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
GROUP BY 1
) AS dt2