User Guide > TDV Caching > Cache Requirements and Limitations > Supported Cache Target Storage Types
 
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
 
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
 
 
Impala
Active
 
Active
 
IBM DB2 LUW v10.5
Active
Active
Active
Native load with insert and select, and DB2 Load are 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 5.1
Active
Active
Active
 
MySQL 5.5
Active
Active
Active
 
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 11g and 11g R2
Active
Active
Active
 
Oracle 12c
Active
Active
Active
 
Oracle 19c
Active
Active
Active
 
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 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 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.