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 |
|
|
|
Active |
Active |
Active |
Native load with insert and select, and the DB2 LOAD utility are supported. |
|
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.