ttOptEstimateStats
ttOptEstimateStats
procedure 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
'
).
The procedure operates on all tables owned by the current user if tblName
is not specified. If the user is the instance administrator, only tables owned by the instance administrator are updated. If the tables are not owned by the user, the user can qualify the table name with their own user name to update stats for the current user.
To determine if your stats are updated, look at the system tables, SYS.COL_STATS
and SYS.TBL_STATS
, before and after you perform this operation.
Required Privilege
This procedure requires no privilege if the user is the table owner, or if tblName
is not specified. This procedure requires the ALTER ANY TABLE
privilege if the user is not the table owner.
Usage in TimesTen Scaleout and TimesTen Classic
This procedure is supported in TimesTen Classic.
TimesTen Scaleout applications can call this builtin procedure.
In TimesTen Scaleout, this procedure runs on all elements in the grid.
Syntax
ttOptEstimateStats(['tblName'], [invalidate], 'sampleStr')
Parameters
ttOptEstimateStats
has these parameters:
Parameter  Type  Description 



Name of an application table. Can include table owner. If Using a synonym to specify a table name is not supported. 


0 (no) or 1 (yes). If The 


String of the form ' 
Result Set
ttOptEstimateStats
returns no results.
Examples
CALL ttOptEstimateStats ( 'ACCTS', 1, '5 PERCENT' ); CALL ttOptEstimateStats ( 'ACCTS', 1, '75 ROWS' );
Notes

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 builtin 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 large or the table in question is small, to improve performance TimesTen computes exact statistics on all columns that have a length of 2,048 bytes or less. For example, the only difference betweenttOptEstimateStats ('ACCTS', 1, '100 PERCENT' )
and
ttOptUpdateStats( 'ACCTS', 1 )
is that the former does not compute statistics for long columns.

The statistics are stored in the
TBL_STATS
andCOL_STATS
system tables. 
For performance reasons,
ttOptEstimateStats
does not hold a lock on tables or rows when computing statistics. However, it holds a lock on the TimesTen system tables. Computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics. 
If you estimate or update statistics with an empty table list, statistics on system tables are updated also, if you have privileges to update the system tables.