All Forums Database
barani_sachin 141 posts Joined 01/12
20 Dec 2013
Finding the table size alone?

Hi All,
How to find the table size alone? Not including the SI's and other stuffs related to a table.

Thanks in advance.

M.Saeed Khurram 544 posts Joined 09/12
20 Dec 2013

Hi Barani,
You can alculate a row size as follows:
Logical size = SUM(Bytes for fixed length columns) + SUM(Byte for Variable Lenght Columns)
Physical size = Logical Size + 14 Bytes overhead + PPI Bytes(Optional) + Compression bits(Optional) + Variable Column Offset(Optional)
Once the row size is calculated, you can calculate the size of table based on row count.
 

Khurram

Raja_KT 1246 posts Joined 07/09
20 Dec 2013

I check it this way:

ct raja_test(id varchar(1)) unique primary index id1(id) say. I dont insert any value(zero rows), then I check :

 

SELECT CurrentPerm

FROM DBC.TablesizeV

WHERE TableName = 'raja_test';

 

Without any value(s) inserted , it gives me the bytes for each amp used. It depends how many amps we have in our system.

 

So, my calculation is this plus physical size of all columns.

 

If your question is how to find tablesize alone, you can get from dbc tablesize.

 

Cheers,

Raja

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
20 Dec 2013

There's a quite unknown COLLECT DEMOGRAPHICS command (to be used by Index Wizard or Visual Explain) which estimates the size of each subtable per AMP.
You don't need a full Query Capture Database (QCD), it's enough to create a single table (copied from the QCD setup script):

CREATE SET TABLE DataDemographics
     (
      MachineName VARCHAR(30) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      TableName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      DatabaseName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC NOT NULL,
      DBSize INTEGER NOT NULL,
      CollectedTime TIMESTAMP(6) NOT NULL,
      AMPNumber INTEGER NOT NULL,
      ClusterNumber INTEGER NOT NULL,
      SubTableID SMALLINT NOT NULL,
      SubTableType VARCHAR(120) CHARACTER SET LATIN NOT CASESPECIFIC,
      RowCount DECIMAL(18,0) NOT NULL,
      AvgRowSize INTEGER NOT NULL,
      QueryID INTEGER,
      IndexName VARCHAR(128) CHARACTER SET UNICODE UPPERCASE NOT CASESPECIFIC,
      DemographicsID INTEGER)
PRIMARY INDEX ( MachineName ,TABLENAME ,DatabaseName );

Then you submit a

COLLECT DEMOGRAPHICS FOR mytable INTO mydatabase; 

Following query returns the approximate size per subtable:

SELECT
   DatabaseName
   ,TABLENAME
   ,SubTableId 
   ,SubTableType
   ,MIN(IndexName) AS IndexName
   ,SUM(RowCount * (AvgRowSize + 0.5)) AS SubTableSize
   ,100 * SubTableSize/CurrentPerm AS SubTablePercent
   ,SUM(SubTableSize) OVER (PARTITION BY DatabaseName, TABLENAME) AS CurrentPerm
FROM DataDemographics
GROUP BY 1,2,3,4
ORDER BY 1,2,3;

Caution: this is not including the size of a Fallback protected table, i.e. you need to add a join to dbc.TablesV and multiply the size times two when it's a Fallback table.

Dieter

M.Saeed Khurram 544 posts Joined 09/12
20 Dec 2013

Sorry, I was thinking with respect to physical database design. 

Khurram

You must sign in to leave a comment.