is there any way to calculate the size of partial records in a table. Like I have flag in a table which identfies whether the row is active (A)or inactive (I). I want to calculate how much space I can save if I physically remove the inactive records which are no longer required.
So I want to calculate the space occupied by active/inactive rows.
calculate space from table where flag_value='I';
Thanks and Regards,
You can get size of a complete table from DBC.Tablesize view.By using this view you can estimate the size of partial data.. lets say you have 100 records in your table which is of 10MB size(Tablesize), out of it lets say you have 50 inactive records so you can say,size of Inactive data records is 5 MB. For accurate estimate I think you have to doTable sizing where you consider datatypes, their length etc..
Query to find Row Size:
databasename LIKE '%XXX%' AND TABLENAME='YYY'
GROUP BY databasename,tablename;
SELECT COUNT(*) FROM TABLE; Y
Here is u r desired result X*Y
By using row size and table size you will able to calculate the size of partial records in a table. Its not 100%
By using row size and table size you will able to calculate the size of partial records in a table.
Below are the steps:
Then total size of rows is X*Y
Yes, I will Agree with you this wont give exact row size, It give avg row size.... Did you have any Solution to find exact row size or to calculate the size of partial records in a table. Please help us with solution....
You can create a PROC which can get the information from dbc.columns and depending on datatype should calculate the max row size. Like if it is Integer add 4 bytes ; for timestamp add 10 bytes.
The easiest way to get an average row size for a populated table is always size/count.
So just get the size from dbc.TableSizeV and calculate the percentage assigned to those active rows based on count(*) vs. Count(active).