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 connector 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 connector 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 connector 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 connector 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 connector creates the cache table based on the SELECT statement without executing the query.
If this option is set, the connector 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 MyCustomReport
Use the following cache statement to cache all rows of a table into the cache table CachedMyCustomReport:
CACHE CachedMyCustomReport SELECT * FROM MyCustomReport
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 CachedMyCustomReport SELECT * FROM MyCustomReport 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 StoreName and Column1 even though the cache table CachedMyCustomReport has all the columns in MyCustomReport.
CACHE CachedMyCustomReport SCHEMA ONLY SELECT * FROM MyCustomReport CACHE CachedMyCustomReport SELECT StoreName, Column1 FROM MyCustomReport