FAQs: SAP HANA View Cache
1. What does view cache allow?
To cache results on SQL views, calculation views and CDS views the SAP HANA view cache (or result cache extension) is used. This allows quick data retrieval without repeating overhead like search and aggregation.
2. When view cache can be used?
In the following scenario the view cache is particularly helpful:
- Based on a view complex query
- Small result is set somewhat
- In the underlying tables limited amount of changes
3. What benefits does the view cache have?
The following advantages can be provided by the view cache:
Reduction of CPU consumption
Reduction of SAP HANA thread utilization
Performance improvements
4. What are the limitations of view cache?
View cache has the following limitations:
As of SAP HANA SPS 11 it is available
5. In what way the view cache can be administered?
The following commands can be used to administer the view cache:
| Command | Details |
| CREATE FUNCTION ... WITH CACHE RETENTION [<minutes>] ... | For a specific object these commands allow activating the view cache. <minutes> defines a change that needs to be reflected in the cache at latest after how many minutes. Every change to an underlying table will result in an immediate cache refresh if minutes is set to 0 or not specified. Additional options like a projection list or filter conditions can be used. |
| ALTER VIEW ... DROP CACHE | An existing view cache is dropped. |
| ALTER SYSTEM CLEAR RESULT CACHE | All view cache entries are removed globally. |
| ALTER SYSTEM REFRESH RESULT CACHE <object_name> | For the specified object the view cache is refreshed. |
| ALTER SYSTEM REFRESH RESULT CACHE ENTRY <cache_id> | With the specified <cache_id> the view cache entry is refreshed. |
| ALTER SYSTEM REMOVE RESULT CACHE ENTRY <cache_id> | With the specified <cache_id> the view cache entry is removed. The cache entry is rebuilt with the next access. |
6. In what way the view cache can be used by a query?
The utilization of the view cache can be controlled by the following hints:
| Hint | Details |
| NO_RESULT_CACHE | Bypass view cache (default) |
| RESULT_CACHE | Force utilization of view cache if available |
Further special hints like RESULT_CACHE_MAX_LAG, RESULT_CACHE_NON_TRANSACTIONAL, RESULT_CACHE_NO_REFRESH, RESULT_LAG, RESULT_CACHE_AFTER_ANALYTIC_PRIVILEGE and RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE are described in the SAP HANA documentation.
7. In what way the view cache can be monitored?
View cache related information by the following monitoring views:
| Monitoring view | Details |
| M_RESULT_CACHE_EXCLUSIONS | Exclude view cache. In this list the views won't be cached. By SAP HANA they are automatically maintained, e.g. in case of large result sets. |
| M_RESULT_CACHE | View cache General information |
| RESULT_CACHE_COLUMNS | In view cache information about columns considered |
| SQL statement | Details |
| SQL: "HANA_Memory_ViewCache" | General view cache information |
View cache specific information is provided by the columns HASH_CACHE, CACHE_RETENTION, CACHE_FILTER and IS_CACHE_FORCED in view VIEWS.
8. For storing view cache information which memory area is used?
In the following heap allocator view cache information is stored:
Pool/RowEngine/ViewCache
9. How the query result cache can be differentiated?
Since SAP HANA SPS 06 the query result cache is available and also caches view results. Listed below are the restrictions that apply:
- If all underlying tables are located in column store then only it is valid
- When one of the underlying table is updated cache is generally updated
- A separate cache entry is there for each query and parameter set
In general the use of view cache is recommended rather than the query result cache.