Size of table with partial data (active records)

Database
Enthusiast

Size of table with partial data (active records)

Hi Experts,

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,

Arindam

6 REPLIES
Enthusiast

Re: Size of table with partial data (active records)

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..

Re: Size of table with partial data (active records)

Query to find Row Size:

SELECT
databasename,
tablename,
SUM(columnlength) ROWSIZE,
COUNT(*) columncount
FROM    
dbc.COLUMNS
WHERE
databasename LIKE '%XXX%' AND TABLENAME='YYY'
GROUP  BY databasename,tablename;

--- X

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:

  1. Find total number of rows in table… assume as X
  2. Find size of the row … assume as Y

Then total size of rows is X*Y

Enthusiast

Re: Size of table with partial data (active records)

SELECT
databasename,
tablename,
SUM(columnlength) ROWSIZE,
COUNT(*) columncount
FROM
dbc.COLUMNS
WHERE
databasename LIKE '%XXX%' AND TABLENAME='YYY'
GROUP BY databasename,tablename;

This method is wrong as it will not give proper rowsize for several datatypes like timestamp, varchar etc.

Re: Size of table with partial data (active records)

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....

Enthusiast

Re: Size of table with partial data (active records)

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.

Junior Contributor

Re: Size of table with partial data (active records)

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).

Dieter