ttOptClearStats

Description

Clears (deletes) the statistics for the specified table, causing the TimesTen query optimizer to use estimates or default values for subsequent queries involving the table. The procedure is useful if statistics are assumed to be out of date and an application wants to use built-in default values. This procedure removes all rows from the TBL_STATS and COL_STATS system tables that pertain to the specified tables. See "SYS.TBL_STATS" and "SYS.COL_STATS" in Oracle TimesTen In-Memory Database SQL Reference.

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

ttOptClearStats('tblName', invalidate)

Parameters

ttOptClearStats 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 or is not specified, statistics are cleared for all of the current user's tables in the data store.

Parameter	Type	Description
`tblName`	TT_CHAR (61)	Name of an application table. Can include table owner. If `tblName` is the empty string or is not specified, statistics are cleared for all of the current user's tables in the data store.
`invalidate`	TT_INTEGER	0 (no) or 1 (yes). Default is 0. If `invalidate` is 1, all commands that reference the affected tables are reprepared automatically when they are re-executed, including commands prepared by other users. If `invalidate` is 0, the statistics are not considered modified and existing commands are not reprepared.

invalidate

TT_INTEGER

0 (no) or 1 (yes). Default is 0.

If invalidate is 1, all commands that reference the affected tables are reprepared automatically when they are re-executed, including commands prepared by other users.

If invalidate is 0, the statistics are not considered modified and existing commands are not reprepared.

Result set

ttOptClearStats returns no results.

Example

CALL ttOptClearStats ( 'SALLY.ACCTS', 1 );

Clears the statistics for the SALLY.ACCTS table and reprepares all commands that affect the ACCTS table.

CALL ttOptClearStats();

Clears the statistics for all of the current user's tables and reprepares all commands that affect these tables.

CALL ttOptClearStats('', 0);

Clears the statistics for all of the current user's tables without repreparing commands that reference these tables.