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
- You must have the required permissions to execute the SQL statement.
- The query that you use to define and create a cache entry must qualify the table name by providing a schema name. For more information, see Creating schemas.
- Review Restrictions for cache entries in Data Virtualization.
About this task
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.
- Go to .
- Click Add new cache in the Active data caches tab.
- Enter valid queries in the SQL editor to create your cache. Note:
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.
- You must include a schema name in your query. For more information, see Creating schemas.
- You cannot include SQL terminators in your query.
- Click Test queries with cache. To find out whether
the cache can match a query (or queries) at runtime:
Your queries display in the Selected queries section. You can edit and remove selected queries before testing query with cache:
- 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.
- 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.
- Click Use this cache to apply cache to the selected queries.
- 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.
- Click Next.
- Enter a name for your cache, ensure that the summary is correct, and click Create cache.