Filter is an optional clause that specifies a filter for the primary table to limit the number of rows that are cached in the primary table. This clause is not supported for views.
Syntax
[FILTER (expression)]
where:
expression
is any valid Where clause. See "Where Clause" for details. Do not include the Where keyword in the clause. The filter for an existing cache can be removed by specifying an empty string for the filter expression, for example, FILTER().
The default value is that cached data is not filtered.
Example A
The following example filters by last activity date.
FILTER (lastactivitydate >= {d'2010-01-01'})
Example B
The following example caches all rows of the account table with a refresh interval of 12 hours, checks whether data of the cached table needs to be refreshed on the first use, persists the data beyond the life of the connection, and stores the data in memory while the connection is active.
CREATE CACHE ON account
Example C
The following example caches all active accounts in the account table with a refresh interval of 1 day, checks whether data of the cached table needs to be refreshed when the connection is established, and discards the data when the connection is closed.
