Supported Cache Target Storage Types

Your cached data can be stored in database tables or as a file. Database caching lets you store result sets in a database so that further manipulations of the result set can be performed in dramatically less time. If any of the cached results are to be used by other views, or be filtered or sorted, then the cache should be stored in a database.

Data from one resource can be cached into one or more cache targets, but multiple resources cannot use the same cache target.

Cached resources are best stored in tabular form. The schema of the resource data must match the schema of the selected data source table. The cache database can have more columns than the source, but the data types of the columns must match. DDL can be generated to create a caching table that matches your view or procedure output.

File storage options are ideal when the results are not sorted or filtered for use in other views. File caches do not store index information, nor do they allow you to push SQL logical operators and filters to the data source. Because there is no index information, any selection of a subset of a view requires loading the cached view into memory for a row-by-row scan. SQL operations are executed on the cached data within TDV memory.

Note: If you build additional queries on large file cache views, significant memory will be required for a full table scan.

TDV supports the following as cache targets:

Cache Target	TDV Support	Parallel Cache Target Support	Native Cache Target Support	Notes
Amazon Redshift	Active	Active	Active
Apache Hive 2.x	Active		Active
Apache Impala 2.x	Active		Active
File	Active	Active		Typically best for demonstrations or caching of a few hundred rows.
Google BigQuery	Active	Active	Active
Greenplum 4.1	Active	Active	Active
Greenplum 4.3	Active	Active	Active
HSQLDB 2.2.9	Active	Active
IBM DB2 LUW v8	Not supported
IBM DB2 LUW v9.5	Active	Active	Active	Native load with insert and select, and the DB2 LOAD utility are supported.
IBM DB2 LUW v10.5	Active	Active	Active	Native load with insert and select, and DB2 Load are supported.
Microsoft SQL Server 2000	Not Supported
Microsoft SQL Server 2005	Not Supported
Microsoft SQL Server 2008	Active	Active	Active	The DBO schema must be selected and introspected as a resource prior to attempting to cache data.
Microsoft SQL Server 2012	Active	Active	Active	The DBO schema must be selected and introspected as a resource prior to attempting to cache data.
Microsoft SQL Server 2014	Active	Active	Active	The DBO schema must be selected and introspected as a resource prior to attempting to cache data.
Microsoft SQL Server 2016	Active	Active	Active	The DBO schema must be selected and introspected as a resource prior to attempting to cache data.
Microsoft SQL Server 2019	Active	Active	Active	The DBO schema must be selected and introspected as a resource prior to attempting to cache data.
MySQL 4.1	Not Supported
MySQL 5.0	Not Supported
MySQL 5.1	Active	Active	Active
MySQL 5.5	Active	Active	Active
Netezza 5.0	Inactive	Inactive	Inactive	Native load with insert and select is supported. Procedure caching is supported.
Netezza 6.0	Active	Active	Active	Native load with insert and select is supported. Parallel cache processing is achieved using the native DISTRIBUTE syntax. Procedure caching is supported.
Netezza 7.0	Active	Active	Active	Native load with insert and select is supported. Parallel cache processing is achieved using the native DISTRIBUTE syntax. Procedure caching is supported.
Oracle 10g	Supported			Native load with INSERT and SELECT is supported. Native load with DB link is not supported.
Oracle 11g and 11g R2	Active	Active	Active
Oracle 12c	Active	Active	Active
Oracle 19c	Active	Active	Active
Oracle 9i	Not Supported			Native load with INSERT and SELECT is supported. Native load with DB link is not supported.
PostgreSQL 9.1	Active	Active	Active	Bulk load is supported. Native loading is supported when the source and target are the same database. If not then Parallel loading is used.
PostgreSQL 9.2.3	Active	Active	Active	Bulk load is supported. Native loading is supported when the source and target are the same database. If not then Parallel loading is used.
SAP HANA SPS 09	Active	Active
Singlestore	Active	Active	Active
Snowflake	Active	Active	Active
Sybase ASE 12.5	Active
Sybase ASE 15.5	Active
Sybase IQ 15.2	Active		Active
Teradata 12	Not Supported
Teradata 13	Active		Active	Supported, but with limitations. If source and target tables are co-located within the same Teradata instance native loading (using INSERT/SELECT statements) will be used, else bulk loading using Teradata FASTLOAD will be attempted.
Teradata 13.10	Active		Active	Supported, but with limitations. If source and target tables are co-located within the same Teradata instance native loading (using INSERT/SELECT statements) will be used, else bulk loading using Teradata FASTLOAD will be attempted.
Teradata 14.10	Active		Active	Supported, but with limitations. Might require Teradata 15 driver. If source and target tables are co-located within the same Teradata instance native loading (using INSERT/SELECT statements) will be used, else bulk loading using Teradata FASTLOAD will be attempted.
Teradata 15	Active		Active	Choose tables For Caching is not supported. If source and target tables are co-located within the same Teradata instance native loading (using INSERT/SELECT statements) will be used, else bulk loading using Teradata FASTLOAD will be attempted.
Teradata 16.20	Active		Active	Choose tables For Caching is not supported. If source and target tables are co-located within the same Teradata instance native loading (using INSERT/SELECT statements) will be used, else bulk loading using Teradata FASTLOAD will be attempted.
ComputeDB	Active	Active	Active
Vertica 5.0	Inactive	Inactive	Inactive	Supports the use of native load and parallel cache load together.
Vertica 6.1	Active	Active	Active	Supports the use of native load and parallel cache load together. Native load with INSERT AND SELECT is supported.

Not all caching tables can support data types from all other supported data tables, and so materialized views with certain data types are not compatible for caching at that database. When a data type mismatch occurs, a warning is issued to the developer to prevent further problems.

Not all data sources that could be used for cache storage can support the generation or execution of DDL statements. In some cases, DDL can be generated and presented, but use of an external tool might be required to create the table metadata. In other cases, the DDL is not shown, because it is not applicable.