Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2013
02:46 AM

06-13-2013
02:46 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

06-13-2013
03:01 AM

06-13-2013
03:01 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-06-2013
09:40 PM

08-06-2013
09:40 PM

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:

- Find total number of rows in table… assume as X
- Find size of the row … assume as Y

Then total size of rows is X*Y

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-07-2013
08:52 AM

08-07-2013
08:52 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-07-2013
08:51 PM

08-07-2013
08:51 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-13-2013
01:39 AM

08-13-2013
01:39 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

08-13-2013
02:18 AM

08-13-2013
02:18 AM

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