ttOptClearStats

This procedure clears 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 System Tables and Views 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.

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 locally on the element from which it is called.

Related Views

This procedure has no related views.

Syntax

ttOptClearStats('tblName', invalidate)

Parameters

ttOptClearStats has these parameters:

Parameter Type Description

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 the current user's tables in the database. Using a synonym to specify a table name is not supported.
`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 rerun, including commands prepared by other users. If `invalidate` is `0`, the statistics are not considered modified and existing commands are not reprepared.

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 the current user's tables in the database.

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

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 rerun, 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.

Examples

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

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

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

CALL ttOptClearStats();

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

CALL ttOptClearStats('', 0);