All Forums Database
sharib hussain 12 posts Joined 01/13
06 Sep 2013
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..
 
 

Thanks & Regards, Sharib Hussain
dnoeth 4628 posts Joined 11/04
11 Sep 2013

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

Dieter

sharib hussain 12 posts Joined 01/13
11 Sep 2013

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 ?
 

Thanks & Regards,
Sharib Hussain

You must sign in to leave a comment.