Skip Headers
Oracle® TimesTen In-Memory Database Reference
Release 11.2.1

Part Number E13069-03
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF



Updates the statistics for the specified table. This procedure estimates statistics by looking at a random sample of the rows in the specified table(s). The sample size is the number of rows specified (if sampleStr has the form 'n ROWS') or a percentage of the total number of rows (if sampleStr has the form 'p PERCENT').

Required privilege

This procedure requires no privilege for the table owner. This procedure requires no privilege if tblName is not specified, because the procedure operates on the current user's tables if tblName is not specified.This procedure requires the ALTER ANY TABLE privilege if user is not the table owner.


ttOptEstimateStats('tblName', invalidate, 'sampleStr')


ttOptEstimateStats has these parameters:

Parameter Type Description
tblName TT_CHAR(61) Name of an application table. Can include table owner. If tblName is the empty string, statistics are estimated for all of the current user's tables in the data store.
invalidate TT_INTEGER 0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when re-executed, including commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared. The invalidate parameter is optional and defaults to 0.
sampleStr TT_VARCHAR (255) NOT NULL String of the form 'n ROWS', where n is an INTEGER greater than zero; or 'p PERCENT', where p is a floating point number between 0.0 and 100.0 inclusive.

Result set

ttOptEstimateStats returns no results.


CALL ttOptEstimateStats ( 'ACCTS', 1, '5 PERCENT' );

CALL ttOptEstimateStats ( 'ACCTS', 1, '75 ROWS' );


The TimesTen statistics include the number of rows in each table, the number of unique values in each column, and the minimum and maximum values in each column. TimesTen assumes a uniform distribution of column values.

This procedure only runs faster than ttOptUpdateStats when you sample less than 50 percent of the rows in the table.

Estimates are not computed on columns that are longer than 2,048 bytes, and statistics for these columns are not updated. To update statistics on columns longer than 2,048 bytes, use the ttOptUpdateStats built-in procedure. (For varying length columns, this procedure updates statistics only if the column has a maximum length of 2,048 bytes or less.)

If a very small value is chosen for the sampleStr parameter, this procedure runs quickly but may result in suboptimal execution plans. For "good" distributions of data, a 10 percent selection is a good choice for computing statistics quickly without sacrificing plan accuracy. If the number of rows specified is sufficiently large or the table in question is sufficiently small, to improve performance TimesTen computes exact statistics anyway on all columns that have a length of 2,048 bytes or less. For example, the only difference between

ttOptEstimateStats ('ACCTS', 1, '100 PERCENT' )


ttOptUpdateStats( 'ACCTS', 1 )

is that the former does not compute statistics for long columns.

The statistics are stored in the TBL_STATS and COL_STATS system tables.

For performance reasons, TimesTen does not hold a lock on tables or rows when computing statistics. However, computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics.

See also