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.