Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

04-15-2009
05:09 AM

04-15-2009
05:09 AM

I know that we can get the space occupied by a table using the following sql :

SELECT sum(CurrentPerm) FROM DBC.TableSize WHERE DatabaseName = 'dbname' and tablename = 'tname';

What is the best way, if we have to calculate the table space manually?

I guess the following is how we calculate it :

TableSize = (RowSize + tdOverHead) * RowCount;

RowSize = sum of the sizes of all the columns in the table

I think there is an overhead for every teradata table row right. I believe it is 13 bytes.

How do I effectively get the row size, since some columns in the table may take variable length. example : varchar.

SELECT sum(CurrentPerm) FROM DBC.TableSize WHERE DatabaseName = 'dbname' and tablename = 'tname';

What is the best way, if we have to calculate the table space manually?

I guess the following is how we calculate it :

TableSize = (RowSize + tdOverHead) * RowCount;

RowSize = sum of the sizes of all the columns in the table

I think there is an overhead for every teradata table row right. I believe it is 13 bytes.

How do I effectively get the row size, since some columns in the table may take variable length. example : varchar.

2 REPLIES

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

04-22-2009
02:25 AM

04-22-2009
02:25 AM

The following will calculate the average rowsize on the existing table. It will take EVERYTHING into account - row header, secondary indices, compression, varchar and unused space in the blocks.

Select Tsz.CPerm / TCnt.NumRows As AvgRow

From

(Select Sum(CurrentPerm) As CPerm

From DBC.Tablesize

WHERE DatabaseName = 'dbname' and tablename = 'tname') Tsz

, (Select Count(*) As NumRows

From dbname.tname) TCnt

;

Select Tsz.CPerm / TCnt.NumRows As AvgRow

From

(Select Sum(CurrentPerm) As CPerm

From DBC.Tablesize

WHERE DatabaseName = 'dbname' and tablename = 'tname') Tsz

, (Select Count(*) As NumRows

From dbname.tname) TCnt

;

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

04-24-2009
06:36 AM

04-24-2009
06:36 AM

But, I also need to know the size occupied by each column. How do i get that?

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.