## 4.4.1.1 Creating and Using the Static Result Cache
This topic gives a number of examples to illustrate how you can use the result cache.
To save memory cached SQL views are based on aggregated results (SUM, MIN, MAX and COUNT are supported); only the aggregated values are stored. The SQL optimizer uses the aggregation type of each column in the top-most select statement as the basis for the cached result.
When a user submits a query to a cached view a matching process analyzes the request and the definition of the query to see if the cached data can be used or if the request must read the data from the database.
To exploit the cached data the query must match with the definition of the result cache and a number of matching rules apply:
- The query must reference the same columns (or a subset of the columns) in the cache.
- The query must explicitly reference the view or a sub view referenced by the view.
- The predicate in the query must be the same as that used in creating the view, any additional conditions which are added must be more restrictive filters.
## Examples
## Aggregation (OF clause)
For SQL views / functions, you can explicitly state the aggregation type as part of the ADD cache clause, then the cache will be defined with an additional aggregation operation on the top for the view definition. This is shown in the following example which creates a view and then uses the alter view syntax to add the view to the cache with a retention period of 120 minutes.
```
CREATE VIEW SIMPLE_VIEW AS (SELECT A, SUM(KF1) AS KF1, MIN(KF2) AS KF2, MAX(KF3) AS KF3 FROM SIMPLE_TABLE GROUP BY A) ALTER VIEW SIMPLE_VIEW ADD CACHE RETENTION 120 OF A, SUM(KF1), MIN(KF2), MAX(KF3);
```
The cache contents will be created from the result of the query: SELECT A, SUM(KF1), MIN(KF2), MAX(KF3) FROM SIMPLE_VIEW GROUP BY A;
In the following example queries, the first two statements consistently use the same aggregated values as defined in the query and can exploit the cache. The third example cannot use the cached data because it requests unaggregated details which are not included in the cache:
```
SELECT SUM(KF1) FROM SIMPLE_VIEW WITH HINT(RESULT_CACHE);
```
```
SELECT SUM(KF1), MIN(KF2), MAX(KF3) FROM SIMPLE_VIEW GROUP BY A WITH HINT(RESULT_CACHE);
```
```
/* only aggregated data is cached - cannot use the cached data*/ SELECT KF1, KF2, KF3 FROM SIMPLE_VIEW WITH HINT(RESULT_CACHE);
```
If the user does not state the aggregation option in the ALTER VIEW statement, the cache will be defined the same as the view definition:
```
ALTER VIEW SIMPLE_VIEW ADD CACHE RETENTION 120;
```
In this case, the cache contents will be created from result of the query: SELECT * FROM SIMPLE_VIEW; . There is then no limitation of cache utilization for queries on SIMPLE_VIEW (as long as RESULT_CACHE hint is used) and the cache would be utilized for all of above three queries.
## Filters (FILTER clause)
This example illustrates filtering. If the user states the additional filter option, the cache will be defined with an additional filter on the top of the view definition:
```
ALTER VIEW SIMPLE_VIEW ADD CACHE RETENTION 120 FILTER B > 3;
```
The cache contents will be created from the result of the query: SELECT * FROM SIMPLE_VIEW WHERE B >= 3;
In the following example queries the first two statements will exploit the cache as those queries requires cache data (B >= 3) only. The third example cannot use the cached data because the query requests data which is not a part of cached data.
```
SELECT SUM(KF1) FROM SIMPLE_VIEW WHERE B > 3 AND B < 10 WITH HINT(RESULT_CACHE);
```
```
SELECT SUM(KF1) FROM SIMPLE_VIEW WHERE B > 3 AND A = 1 WITH HINT(RESULT_CACHE);
```
```
/* only B > 3 data is cached - cannot use the cached data*/ SELECT KF1, KF2, KF3 FROM SIMPLE_VIEW WHERE A = 1 WITH HINT(RESULT_CACHE);
```
## Cached Views and Analytic Privileges
By default a cache entry is created for each analytic privilege which in some cases may lead to a large number of cache entries. To avoid this situation and to provide some flexibility an alternative scheme is available which can be applied by either setting a configuration parameter or using the hint HINT(RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE) . With this option one cache entry is created without applying analytic privileges and the privilege is then applied on top of the cache for every query execution and the coverage of analytic privilege type is also changed. By default, only static XML analytic privileges are supported, but using the configuration parameter or hint this limitation is removed and dynamic XML analytic privileges and SQL analytic privileges are also supported.
This feature can be enabled using the following configuration setting in the [result_cache] section of the indexserver.ini file:
| Parameter                 | Default   | Detail                                                                                           |
|---------------------------|-----------|--------------------------------------------------------------------------------------------------|
| before_analytic_privilege | False     | Set this to True to enable the option of caching the result before applying analytic privileges. |
The following hints are supported:
- HINT(RESULT_CACHE_BEFORE_ANALYTIC_PRIVILEGE) - this hint builds the cache before the authorization restrictions are applied (that is, read all the data and apply the restrictions when the data is queried so that only authorized data is selected for each user).
- HINT(RESULT_CACHE_AFTER_ANALYTIC_PRIVILEGE) - this hint builds the cache in the normal way by applying analytic privileges first. This option is more efficient in terms of memory usage since less data is cached but is only suitable for views where static XML privileges have been applied.
## Result Cache HDI Plugin
To transform and deploy design time DDL-based result cache definitions the result cache plugin is available. The following example defines a result cache for the view named 'A_Sql_View':
```
RESULT CACHE "_SYS_CACHE#sap::A_Sql_View" ON VIEW "sap::A_Sql_View" WITH RETENTION 30
```
This DDL-style syntax is the same as SQL except that no CREATE / ADD keyword is required. The complete name of the cache is specified as: "_SYS_CACHE#&lt;view name>=".
To cache a table function use ON FUNCTION instead of ON VIEW. Any values after the keyword WITH are copied to the corresponding SQL statement, so other parameters such as defining a projection list, a filter or the force flag are also supported.
Refer to the SAP HANA Developer Guide For SAP HANA XS Advanced Model for further details.