Full Mode

When a view uses the Full cache mode, the data of the view is always retrieved from the cache database instead of from the source.

The main benefit of this mode over the partial cache is that complex operations (joins, unions, group by …) involving several views (even from different data sources) can be delegated to the cache database. Therefore, the performance of these operations is significantly improved.

With this cache mode, you have to execute a special query (“Cache preload query”) to fill the cache of the view. The results of this query are inserted in the cache and any query to this view will be based on these data.

The “Cache preload query” is a regular query with the parameter 'cache_preload' = 'true' in the CONTEXT clause. For example:

SELECT *
FROM internet_inc
CONTEXT (
      'cache_preload' = 'true'
    , 'cache_invalidate' = 'all_rows'
    , 'cache_wait_for_load' = 'true'
    , 'cache_return_query_results' = 'false')

The “Cache preload query” has to meet the following conditions:

  1. It has to project all the fields of the view. I.e.: SELECT * FROM ...
  2. The preload query cannot have aggregation clauses or aggregation functions. I.e. GROUP BY or HAVING are forbidden. It can have selection conditions (WHERE clause)
  3. The preload query must be executed over the view that has the full cache mode enabled. It must not be executed over the views derived from the view that has the full cache mode enabled.
  4. The preload query cannot have subqueries in the WHERE clause.

The section Recommended Parameters for Queries that Load the Cache lists the recommended parameters for “Cache preload queries”.

You will have to execute these queries periodically. To do this, we recommend using the Denodo Scheduler because it provides a wizard where you can select the views whose cache you want to preload, how often and select the parameters used in these queries.

The following sections describe two strategies that can be followed to preload the cache of a view with “Full” cache:

  1. Loading the cache invalidating the existing data: see section Loading the Cache Invalidating the Existing Data.
  2. Loading the cache incrementally: see section Loading the Cache Incrementally.

Loading the Cache Invalidating the Existing Data

By adding the parameters ('cache_preload' = 'true',  'cache_invalidate' = 'all_rows') to the CONTEXT clause of the query, the Cache Engine marks as invalid all the cached data of the queried view and then, stores the result of the query in the cache database.

For example, this query invalidates all the cached rows for the view phone_inc and then, caches the result of the query:

SELECT *
FROM phone_inc
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'all_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'false')

Loading the Cache Incrementally

In some scenarios, when a view uses the Full cache mode, you may need to load the cache of the view by executing more than one query. For example, because doing it with a single query would put too much load on the source. In this scenario, you cannot use the parameter 'cache_invalidate' = 'all_rows' because it invalidates all the view’s cache.

Virtual DataPort provides two other alternatives to 'cache_invalidate' = 'all_rows':

  1. Add the parameters ('cache_preload' = 'true', 'cache_invalidate' = 'matching_rows'): the Cache Engine invalidates the cached rows that match the WHERE clause of the query and then, caches the result of the query. Adding these parameters to the query is equivalent to executing the view from the “Execute” dialog of the view and selecting the “Store results in cache” and the “Invalidate existing results” check boxes.

    For example, let us say that you have a table order whose cache mode is full and you need to update the data cached for this view in order to have the most recent records for the current year. However, you do not want to invalidate the cached data for the previous years because it has not changed and retrieving it all again it would take too much time. With the query below, you achieve this: it only returns the orders whose date is the current year and as it has the parameter 'cache_invalidate' = 'matching_rows', the Cache Engine only invalidates the rows that match the condition of the query and caches the data obtained from the source.

    SELECT *
    FROM order
    WHERE order_date >= TRUNC( CURRENT_DATE(), 'Y')
    CONTEXT (
    'cache_preload' = 'true'
    , 'cache_invalidate' = 'matching_rows'
    , 'cache_wait_for_load' = 'true'
    , 'cache_return_query_results' = 'false')
    
  2. Add the parameter ('cache_preload' = 'true') to the CONTEXT clause of the query but not 'cache_invalidate': the result of the query is stored in the cache database, without invalidating the current cached data. By using this parameter and executing several queries, the cache can be loaded incrementally.

    Adding this parameter is equivalent to executing the view from the “Execute” dialog of the view, selecting the “Store results in cache” check box and clearing the “Invalidate existing results” one. When doing this, you need to make sure that the results of the queries you use to load the cache incrementally, do not overlap. For example, if you execute the following queries:

    SELECT *
    FROM phone_inc
    WHERE pinc_id > 0
    CONTEXT (
    'cache_preload' = 'true'
    , 'cache_wait_for_load' = 'true'
    , 'cache_return_query_results' = 'false')
    

    And,

    SELECT *
    FROM phone_inc
    WHERE pinc_id > -1
    CONTEXT (
    'cache_preload' = 'true'
    , 'cache_wait_for_load' = 'true'
    , 'cache_return_query_results' = 'false')
    

The cache will store the data returned by both queries. Then, if you execute the query SELECT * FROM phone_inc, the result will be union of these queries, which will contain the same rows twice.

The Full Cache Mode At Runtime

When querying a view with Full cache enabled, the data is always retrieved from the cache, even if no cache preload queries have been executed.

In terms of performance, the main benefit of using the Full cache mode is that if you combine two views with cache Full enabled, the query is delegated to the cache. This may result in a faster execution time. This optimization is not available when using the Partial cache mode.

For example, if the full cache mode is enabled in the views internet_inc and phone_inc (these two views were created in the section Creating Base Views from a JDBC Data Source) and you execute a JOIN between them, the join operation will be delegated to the cache database instead of executing it in the Server.

Important

When this cache mode is enabled, the cache engine does not check if the query used to fill in the cache retrieved all the data from the source. For example, if the query was loaded with this query:

SELECT *
FROM phone_inc WHERE pinc_id > 1
CONTEXT ('cache_preload' = 'true', 'cache_invalidate' = 'all_rows')

And later execute the query

SELECT *
FROM phone_inc

The data is retrieved from the cache, which means that you will only obtain the rows that match the condition pinc_id > 1.