table size without indexes

Database
Enthusiast

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

Re: table size without indexes


From screen 6 of the DBW program.

start ferret

this will start the Ferret application in one of the DBW application windows.

at the ferret prompt type

scope table "mydatabase.mytable" 0
showblocks
quit

The 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 /m

will also display lines for each index which you can multiply the same way.

----

Enthusiast

Re: table size without indexes

Hi

I 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, currentperm
from dbc.tablesize
where databasename = 'DBA'
and tablename = 'testing'

You can see the space used on each AMP.

Divvy
Enthusiast

Re: table size without indexes

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 ?
Enthusiast

Re: table size without indexes



You can run ferret from Teradata Manager.

Re: table size without indexes

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
Enthusiast

Re: table size without indexes

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

Re: table size without indexes

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.