Source Database: DB2
I have a view say top_level_view, which takes a long time to query and aggregate from the DB2 database. We decided to cache this view with a where clause for 15K keyids. To begin with we tried to run the view without caching to see whether it completes.
```
SELECT * FROM top_level_view
where key_id in
(
select key_id from distinct_view -- This returns around 15K unique key_id
)
```
The above query runs beyond the allowed 15 minutes limit in Denodo and fails. Also, there are some restrictions in DB2 which terminates the process when it reaches specific resource limitation. So, as the next step we extracted those 15K key_id into spreasheet and manually tried and we found around 4k key_ids can be passed to run successfully without breaking both Denodo and DB2 restrictions.
Cache DB: Oracle
Cache Type: Full Cache, Never Expires
```
SELECT * FROM top_level_view
where key_id in
(
-- 4K key_ids passed manually
)
CONTEXT ('i18n'='us_est', 'cache_preload'='true', 'cache_wait_for_load'='true')
```
Based on the baseline and before we create a scheduler job, we wanted to test it with VQL and it started failing again as it went beyond both Denodo and DB2 limits. So, we again did some trial and error and found 500 - 700 key_ids would work successfully for the cache process.
So, here are my questions
1. Why the view runs successfuly without cache for 4k records and fails when we try to cache it? We understood some invalidation updates in cache is causing this, but not sure whether that is the reason.
2. If we need to load 500-700 keys in a batch, then what are the options we have available. This job needs to run periodiocally to keep the cache upto date.
There is no option for us to consider incremental caching.
Please suggest how to approach this and the best practices.
Update: Used 'cache_return_query_results' = 'false' and don't see any noticable difference.
