ttOptSetColIntvlStats

This procedure modifies the statistics for the specified columns with interval information. This procedure enables an application to set statistics manually rather than have TimesTen automatically compute them. This feature is useful for preparing commands before the data has been inserted or for seeing how table characteristics can affect the choice of execution plan. This procedure modifies the relevant row(s) in the COL_STATS system table. Modifying interval statistics for a column that is not currently indexed has no effect.

Because this procedure can be used before any data is in the table, the values specified do not need to bear any relation to the actual values, although some basic validity checking is performed.

Required Privilege

This procedure requires no privilege (if owner) or ALTER ANY TABLE privilege (if not 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

ttOptSetColIntvlStats('tblName', 'colName', invalidate, (stats))

Parameters

ttOptSetColIntvlStats has these parameters:

Parameter Type Description

tblName

TT_CHAR(61) NOT NULL

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

colName

TT_CHAR(30) NOT NULL

Name of a column in that table.

invalidate

TT_INTEGER

0 (no) or 1 (yes). If invalidate is 1, all commands that reference the affected tables are automatically prepared again when rerun. This includes commands prepared by other users. If invalidate is 0, the statistics are not considered to have been modified and existing commands are not reprepared.

stats

VARBINARY (409600) NOT NULL

Sets stats for the column, using the format:

(numInterval integer, numNull integer, totUniq integer, totTups integer,

/* information for interval 1 */

(numUniq integer, numTups integer, frequency of most occurred value integer, minVal, maxVal, modalVal),

/* information for interval 2 */...)

The numUniq value is the number of unique values minus 1.

The numTups value is the number of rows whose value is not the modal value.

The modal value (modalVal) is the value that occurs most often in a specified interval.

Because this parameter is a compound structure it cannot be parameterized using ODBC functions or described using the ttIsql describe command. For example, a statement like the following fails: SQLPrepare(hstmt, "call ttOptSetColIntvlStats('t1', 'c1', 1, ?)", SQL_NTS)).

Result Set

ttOptSetColIntvlStats returns no results.

Examples

To set the following statistics for column t1.x1:

  • Two intervals

  • Integer type

  • 10 rows with null value

  • 10 unique value

  • 100 rows

  • Interval 1 (4 unique values besides the most frequently occurring value, 40 rows with values other than most frequently occurring value, 10 rows with most frequently occurring value, min = 1, max = 10, mod = 5)

  • Interval 2 (4 unique values besides the most frequently occurring value, 20 rows with values other than most frequently occurring, 20 rows with most frequently occurring value, min = 11, max = 20, mod = 15)

Use the statement:

CALL ttOptSetColIntvlStats('t1', 'x1', 1, (2, 10, 10, 100, 
(4, 40, 10, 1, 10, 5), (4, 20, 20, 11, 20, 15)));

Note:

You must specify the minimum and maximum values in the interval as VARBINARY. NULL values are not permitted as minimum or maximum values. The value is stored in the platform-specific endian format.