Finding the table size alone?

Database
Enthusiast

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

Re: Finding the table size alone?

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
Enthusiast

Re: Finding the table size alone?

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

Senior Apprentice

Re: Finding the table size alone?

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.

Enthusiast

Re: Finding the table size alone?

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

Khurram