Adding data caches (Data Virtualization)

Data Virtualization Admins can create a cache entry to save query data and results and optimize query performance.

Before you begin

Ensure that you meet the following requirements:

About this task

Data Virtualization users require interactive query performance to access to data of any size, any format, and any source. To avoid query executions that are time-consuming, Data Virtualization Admins can create a cache entry to improve the performance by caching the result set of your queries.

It is recommended to limit the number cache entries, irrespective of their size. Having many cache entries increases the cost of compiling and evaluating user queries, and decreases service performance.


  1. Go to Data > Data Virtualization > Virtualization > Cache management.
  2. Click Add new cache in the Active data caches tab.
  3. Enter valid queries in the SQL editor to create your cache.
    • You must include a schema name in your query. For more information, see Creating schemas.
    • You cannot include SQL terminators in your query.
    You can use the Syntax assistant to facilitate the writing of SQL statements. Additionally, you can filter and search the content of your SQL statements.
    Restriction: Cache entries are subject to certain restrictions in Data Virtualization. For more information, see Restrictions for cache entries.
  4. Click Test queries with cache. To find out whether the cache can match a query (or queries) at runtime:
    • Enter query in the SQL editor.
    • Go to the Select from existing tab and choose the queries for which you want to test cache impact.
      Tip: You can filter existing queries by creation date, creator, execution time, and types of query.
    Your queries display in the Selected queries section. You can edit and remove selected queries before testing query with cache:
    1. Click Test queries to estimate impact of cache on your selected queries.
      The Status column in the Selected queries section provides the following information:
      Matched to cache
      Data Virtualization determined that the cache matched with the selected queries, and hence, if you choose to create the cache, those queries are likely to use it at runtime. Whether query performance improves or not depends on other factors in the overall query plan.
      Note: There is no guarantee that queries will use the cache. There can be other near-identical caches that might override the utility of the cache that you are creating.
      Not matched to cache
      Data Virtualization determined that the cache does not match the selected queries, and hence, might not have any influence on query performance.
      Data Virtualization could not validate whether the selected queries match the cache or not. Refer to the error message for details.

      To configure cache recommendations, see Configuring cache recommendations.

    2. Click Use this cache to apply cache to the selected queries.
  5. Click Next. To set a refresh rate for the cache:
    A frequent refresh prevents the cache from becoming stale in time. To set refresh frequency:
    • Hourly: Select time (minute) of the hour to refresh your cache.
    • Daily: Select days of the week and time of the day to refresh your cache.
    • Weekly: Select day of the week and time of the day to refresh your cache.
    • Monthly: Select weeks of the month, days of the week, and time of the day to refresh your cache.
    • None: If you select this option, you must refresh the cache manually.
  6. Click Next.
  7. Enter a name for your cache, ensure that the summary is correct, and click Create cache.