ttCacheAutorefreshSelectLimit

Configuring the incremental autorefresh to join the Oracle database base table with a limited number of rows from the autorefresh change log table is known as configuring a select limit.

Required Privilege

This procedure requires the ADMIN or CACHE_MANAGER privileges.

Usage in TimesTen Scaleout and TimesTen Classic

This procedure is supported in both TimesTen Classic and TimesTen Scaleout.

Related Views

This procedure has no related views.

Syntax

ttCacheAutorefreshSelectLimit ( autorefreshInterval, value )

Parameters

ttCacheAutorefreshSelectLimit has the parameters:

Parameter Type Description

Parameter	Type	Description
`param`	`VARCHAR2 (50) NOT NULL`	The `autorefreshInterval` designates the cache group (the one with this autorefresh interval value) on which to apply the `value`. The integer value for the autorefresh interval (in milliseconds) is the same value that was originally specified when the autorefresh cache group was created to indicate how often autorefresh is scheduled.
`value`	`VARCHAR2 (200)`	The `value` denotes a limit of the number of rows to select from the autorefresh change log file to apply to the cached table. These changes are applied incrementally until all the rows in the autorefresh change log table have been applied. If the value changes, it takes effect at the start of the next autorefresh cycle. The `value` can be one of the following: '`ON`': Select at most 1000 rows at a time from the autorefresh change log table to apply for every autorefresh cycle. `number`: Select at most a user specified number of rows from the autorefresh change log table during the autorefresh cycle. If the user specified a limit size of 2000 rows, then autorefresh selects at most 2000 rows at a time from the autorefresh change log table. If you specify a negative number, an error is returned. '`OFF`': Disables the select limit. The incremental autorefresh selects all rows from the change log table during the autorefresh cycle. `NULL`: If the `value` provided is `NULL` or not specified, the current setting is returned.

param

VARCHAR2 (50) NOT NULL

The autorefreshInterval designates the cache group (the one with this autorefresh interval value) on which to apply the value.

The integer value for the autorefresh interval (in milliseconds) is the same value that was originally specified when the autorefresh cache group was created to indicate how often autorefresh is scheduled.

value

VARCHAR2 (200)

The value denotes a limit of the number of rows to select from the autorefresh change log file to apply to the cached table. These changes are applied incrementally until all the rows in the autorefresh change log table have been applied.

If the value changes, it takes effect at the start of the next autorefresh cycle.

The value can be one of the following:

'ON': Select at most 1000 rows at a time from the autorefresh change log table to apply for every autorefresh cycle.
number: Select at most a user specified number of rows from the autorefresh change log table during the autorefresh cycle. If the user specified a limit size of 2000 rows, then autorefresh selects at most 2000 rows at a time from the autorefresh change log table. If you specify a negative number, an error is returned.
'OFF': Disables the select limit. The incremental autorefresh selects all rows from the change log table during the autorefresh cycle.
NULL: If the value provided is NULL or not specified, the current setting is returned.

Result Set

ttCacheAutorefreshSelectLimit returns the select limit value that has been set for a particular autorefresh interval:

Column	Type	Description
`param`	`VARCHAR2(50)`	The `autorefreshInterval` that designates the cache group (the one with this autorefresh interval value).
`value`	`VARCHAR2(200)`	The current `value` that shows the number of rows that is selected from the autorefresh change log file to apply to the cached table.

Examples

You can show the current setting by either providing a NULL value or no parameter. The following example shows the setting for incremental autorefresh cache groups with an interval value of 7 seconds.

Command> call ttCacheAutorefreshSelectLimit('7000', NULL);
< 7000, 2000 >
1 row found.
Command> call ttCacheAutorefreshSelectLimit('7000');
< 7000, 2000 >
1 row found.

The following example set a select limit to 2000 rows for incremental autorefresh cache groups with an interval value of 7 seconds.

Command> call ttCacheAutorefreshSelectLimit('7000', '2000');
< 7000, 2000 >
1 row found.

Notes

This procedure is available only for cache operations.
The ttCacheAutotrefreshSelectLimit built-in procedure can set a select limit only on an interval that is defined for a single cache group that contains one table, where the cache group is defined as a static read-only cache group with incremental autorefresh.
The setting for ttCacheAutorefreshSelectLimit is not replicated or duplicated. The user must execute the built-in on both the active and standby nodes.
The settings do not reset if you drop all cache groups for the interval.
The ttMigrate, ttBackup, and ttRestore built-in procedures do not preserve the setting of ttCacheAutorefreshSelectLimit.
If you alter the cache group autorefresh interval, it does not modify what was set previously through execution of ttCacheAutorefreshSelectLimit for the cache group. You can only alter the select limit for the cache group with the ttCacheAutorefreshSelectLimit built-in procedure.