ttOptUpdateStats

Description

Updates the statistics for the specified table. TimesTen looks at the data stored 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.

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.

Syntax

ttOptUpdateStats('tblName', invalidate, option)

Parameters

ttOptUpdateStats has these parameters:

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 of the current user's tables are updated.

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 of the current user's tables are updated.
`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.
`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 t-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. See "Notes" for more information.

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.

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 t-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.

See "Notes" for more information.

Result set

ttOptUpdateStats returns no results.

Example

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 executed.

CALL ttOptUpdateStats('', 1);

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

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 of 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 NULLs in the column
Total number of NON NULL UNIQUE values in the column
Total number of rows in the table
Interval information, 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, computing statistics can still slow performance. Estimating statistics generally provides better performance than computing exact statistics. See "ttOptEstimateStats" for information on estimating statistics.