When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The component updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.
See Caching Data for more information on different caching strategies.
CACHE Statement Syntax
The cache statement may include the following options that alter its behavior:
CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement>
If this option is set, the component removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.
If this option is set, the component commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.
If this option is set, the component drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.
If this option is set, the component creates the cache table based on the SELECT statement without executing the query.
If this option is set, the component alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.
Use the following cache statement to cache all rows of a table:
CACHE SELECT * FROM SampleTable_1
Use the following cache statement to cache all rows of a table into the cache table CachedSampleTable_1:
CACHE CachedSampleTable_1 SELECT * FROM SampleTable_1
Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.
CACHE CachedSampleTable_1 SELECT * FROM SampleTable_1 WHERE DateModified > '2013-04-04'
Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only Id and Column1 even though the cache table CachedSampleTable_1 has all the columns in SampleTable_1.
CACHE CachedSampleTable_1 SCHEMA ONLY SELECT * FROM SampleTable_1 CACHE CachedSampleTable_1 SELECT Id, Column1 FROM SampleTable_1