ttSize

Description

This procedure estimates the size of a table or view. It returns a single row with a single DOUBLE column with the estimated number of bytes for the table. The table can be specified as either a table name or a fully qualified table name. A non-NULL nrows parameter causes the table size to be estimated assuming the statistics of the current table scaled up to the specified number of rows. If the nrows parameter is NULL, the size of the table is estimated with the current number of rows.

The current contents of the table are scanned to determine the average size of each VARBINARY and VARCHAR column. If the table is empty, the average size of each VARBINARY and VARCHAR column is estimated to be one-half its declared maximum size. The estimates computed by ttSize include storage for the table itself, VARBINARY and VARCHAR columns and all declared indexes on the table.

The table is scanned when this built-in procedure is called. The scan of the table can be avoided by specifying a non-NULL frac value, which should be between 0 and 1. This value is used to estimate the average size of varying-length columns. The maximum size of each varying-length column is multiplied by the frac value to compute the estimated average size of VARBINARY or VARCHAR columns. If the frac parameter is not given, the existing rows in the table are scanned and the average length of the varying-length columns in the existing rows is used. If frac is omitted and the table has no rows in it, then frac is assumed to have the value 0.5.

Required privilege

This procedure requires the SELECT privilege on the specified table.

Syntax

ttSize('tblName', nRows, frac)

Parameters

ttSize has these parameters:

Parameter	Type	Description
`tblName`	TT_CHAR(61) NOT NULL	Name of an application table. Can include table owner. This parameter is required.
`nRows`	TT_INTEGER	Number of rows to estimate in a table. This parameter is optional.
`frac`	BINARY_DOUBLE	Estimated average fraction of VARBINARY or VARCHAR column sizes. This parameter is optional.

Result set

ttSize returns the result set:

Column	Type	Description
`size`	BINARY_DOUBLE NOT NULL	Estimated size of the table, in bytes.

Examples

CALL ttSize('ACCTS', 1000000, NULL);

CALL ttSize('ACCTS', 30000, 0.8);

CALL ttSize('SALES.FORECAST', NULL, NULL);

When using ttSize, you must first execute the command and then fetch the results. For example:

ODBC

double size;
SQLLEN len;

rc = SQLExecDirect(hstmt, "call ttSize('SalesData', 250000, 
0.75)", SQL_NTS);
rc = SQLBindColumn(hstmt, 1, SQL_C_DOUBLE, &size, sizeof double, 
&len);
rc = SQLFetch(hstmt);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);

JDBC

. . . . . .
String URL="jdbc:timesten:MyDataStore";
Connection con;
double tblSize=0;
. . . . . .
con = DriverManager.getConnection(URL);
CallableStatement cStmt = con.prepareCall("
{CALL ttSize('SalesData', 250000, 0.75) }");
if( cStmt.execute() ) 
  {
   rs=cStmt.getResultSet();
   if (rs.next()) {
    tblSize=rs.getDouble(1);
   }
   rs.close();
  }
cStmt.close();
con.close();

. . . . . .

Note

The ttSize procedure allows you to estimate how large a table will be with its full population of rows based on a small sample. For the best results, we recommend populating the table with at least 1,000 typical rows.