All Forums Database
phil1212 5 posts Joined 11/05
02 Jan 2006
table size without indexes

Hi folks and hapy new year,Could you tell me how can i get the exact table size, but without indexes size ?select sum(currentperm) from dbc.allspace where databasename = 'XXXX' and tablename = 'MATABLE' => gives me all space.Do you know the table name for what i need ?Thanks.

Dennis Calkins 12 posts Joined 07/04
02 Jan 2006

From screen 6 of the DBW program. start ferretthis will start the Ferret application in one of the DBW application windows.at the ferret prompt typescope table "mydatabase.mytable" 0showblocks quitThe display is very wide. On the right hand side you will find Number of datablocks and Average Datablock size.Multiply the 2 numbers together and you have the size of the table in Sectors ( Divide the answer by 2 to get Kilobytes ) showblocks /mwill also display lines for each index which you can multiply the same way.----

XTUPIE 42 posts Joined 11/05
03 Jan 2006

HiI generally use the following query to determine table sizes.select databasename, tablename, sum(currentperm)from dbc.tablesize where databasename = 'DBA' and tablename = 'testing' group by databasename , tablename;This will give you the space size of all your part of your table across all AMPS. If you change the query to the follwing below:select databasename, tablename, currentpermfrom dbc.tablesize where databasename = 'DBA' and tablename = 'testing'You can see the space used on each AMP.Divvy

Regards
Divvy

phil1212 5 posts Joined 11/05
03 Jan 2006

Ok thanks.I need just an sql query, only dba have the tools gives in the first answer.For the second, are you sure this doesn't include indexes size ?

Dennis Calkins 12 posts Joined 07/04
03 Jan 2006

You can run ferret from Teradata Manager.

stuffie0912 4 posts Joined 06/10
16 Jun 2010

Using the example above...(shown below)

Can I calculate total table size for all tables of a specific user?

_____
select databasename, tablename, sum(currentperm)
from dbc.tablesize
where databasename = 'DBA'
and tablename = 'testing'
group by databasename , tablename;

This will give you the space size of all your part of your table across all AMPS. If you change the query to the follwing below:

select databasename, tablename, currentperm
from dbc.tablesize
where databasename = 'DBA'
and tablename = 'testing'
_____

Thanks,

Stephanie

robpaller 159 posts Joined 05/09
02 Jul 2010

Index space is included in the DBC.TableSize. The exact formulas for calculating index sizes are found in Chapter 15 of the Database Design manual. Can can use (8 * Table Cardinality) to get a rough estimate as well.

logc 34 posts Joined 09/05
19 Aug 2011

Hi Dennis,
Do you know the command to show all block level compressed tables or how to tell if a table is BLC'd in Ferrett? Thanks.

You must sign in to leave a comment.