ttOptUpdateStats

This procedure updates the statistics for the specified table. TimesTen looks at the data in the table and updates the TBL_STATS and COL_STATS system tables. If the table is large, this process can take some time. Statistics are not computed automatically as rows are updated; an application must compute them explicitly by calling this procedure.

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 built-in procedure.

In TimesTen Scaleout, this procedure runs on all elements in the grid.

Related Views

This procedure has no related views.

Syntax

ttOptUpdateStats(['tblName'], [invalidate], [option])

Parameters

ttOptUpdateStats has these parameters:

Parameter Type Description

Parameter	Type	Description
`tblName`	`TT_CHAR(61)`	Name of an application table. Can include table owner. If a value of `NULL` or an empty string is provided, the statistics for all the current user's tables are updated. Using a synonym to specify a table name is not supported.
`invalidate`	`TT_INTEGER`	0 (no) or 1 (yes). If `invalidate` is 1, marks all commands for reprepare on next run except `ALTER TABLE DROP TABLE`, and the `ALTER TABLE ADD COLUMN FOR SELECT * FROM TABLE` statements. These exceptions require manual reprepare. 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.
`option`	`TT_INTEGER`	Specifies whether to collect complete interval statistics information. Valid values for this option are: `NULL` or 0 - Collect complete interval statistics only if a range index exists on the column. If a range index does not exist, only single interval statistics are collected. 1 - Do not collect complete interval statistics. Only single interval statistics are collected. The `option` parameter is optional and defaults to 0. See the notes below for more information.

tblName

TT_CHAR(61)

Name of an application table. Can include table owner. If a value of NULL or an empty string is provided, the statistics for all the current user's tables are updated.

Using a synonym to specify a table name is not supported.

invalidate

TT_INTEGER

0 (no) or 1 (yes). If invalidate is 1, marks all commands for reprepare on next run except ALTER TABLE DROP TABLE, and the ALTER TABLE ADD COLUMN FOR SELECT * FROM TABLE statements. These exceptions require manual reprepare. 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.

option

TT_INTEGER

Specifies whether to collect complete interval statistics information. Valid values for this option are:

NULL or 0 - Collect complete interval statistics only if a range index exists on the column. If a range index does not exist, only single interval statistics are collected.

1 - Do not collect complete interval statistics. Only single interval statistics are collected.

The option parameter is optional and defaults to 0.

See the notes below for more information.

Result Set

ttOptUpdateStats returns no results.

Examples

CALL ttOptUpdateStats ( 'ACCTS', 1 );

Updates the ACCTS table and causes all commands that reference the ACCTS table to be re-prepared when they are next run.

CALL ttOptUpdateStats('', 1);

Updates all the current user's tables and causes commands on those tables to be reprepared when they are next run.

CALL ttOptUpdateStats('ACCTS', 0, 1);

Forces single interval statistics to be collected.

Notes

If the table name specified is an empty string, statistics are updated for all the current user's tables.

When complete interval statistics are collected, the total number of rows in the table is divided into 20 or less intervals and the distribution of each interval is recorded in the statistics. The new statistics contain the information:

Number of intervals
Total number of NULL values in the column
Total number of NON NULL UNIQUE values in the column
Total number of rows in the table
Interval information, where each interval contains:
- The minimum value
- The maximum value
- The most frequently occurring value
- The number of times the most frequent value occurred
- The number of rows that have different values than the most frequent value
- The number of unique values besides the most frequent value

Collection of complete interval statistics requires the data to be sorted.

If complete interval statistics are not selected, then statistics are collected by treating the entire distribution as a single interval.

For performance reasons, TimesTen 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. See ttOptEstimateStats for information on estimating 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.