How to find table size and index size using Ferret utility

Database
Enthusiast

How to find table size and index size using Ferret utility

Hi All,

Can anyone tell me how to find the index size and table size using Ferret Utility..

Also what are the other methods to find out index size apart of creating the table with and without index and then take the difference of both..

I also tried using COLLLECT DEMOGRAPHICS but i dont think it is giving me the exact index size as the calculation is based on the average values..

2 REPLIES
Junior Contributor

Re: How to find table size and index size using Ferret utility

Hi Sharib,

COLLECT DEMOGRAPHICS is usually quite close to the actual size (but i doesn't include Fallback size), i use a query like this to get a percentage for each SI:

SELECT
dd.DatabaseName
,dd.TableName
,i.IndexNumber
,dd.IndexName
,dd.SubTableId
,dd.SubTableType
,dd.SubTableSize
,dd.CurrentPerm
,100*SubTableSize/CurrentPerm
FROM
(
SELECT
DatabaseName
,TableName
,SubTableId
,SubTableType
,MIN(IndexName) AS IndexName
,SUM(RowCount * (AvgRowSize+0.5)) AS SubTableSize
,SUM(SubTableSize) OVER (PARTITION BY DatabaseName, TableName) AS CurrentPerm
FROM DataDemographics
GROUP BY 1,2,3,4
) AS dd
LEFT JOIN DBC.Dbase AS db
ON dd.DatabaseName = db.DatabaseName
LEFT JOIN DBC.TVM AS t
ON db.DatabaseId = t.DatabaseId
AND dd.TableName = t.TVMName
LEFT JOIN dbc.Indexes AS i
ON t.TVMId = i.TableId
AND i.FieldPosition = 1
AND CASE
WHEN dd.SubTableID = 1024 THEN 1
ELSE dd.SubTableID-1024
END = i.IndexNumber
ORDER BY 1,2,3
;

Using Ferret you have to look at the output of "SHOWB /l" to get the exact number of datablocks of each size for each subtable or "SHOWB /m" for an average size and then do some math.

Dieter

Enthusiast

Re: How to find table size and index size using Ferret utility

Thanks Dieter... 

I am new to teradata and never used the ferret to find tha datablock size. Can you please tell me little bit in detail of the maths which i can use to find out the index size..  How to move ahead with the figures i got by SHOWB /I ot SHOWB/m ?