https://community.denodo.com/kb/en/view/document/Cache%20database%20size%20estimate
ビュー名とキャッシュテーブル名の対応確認
select * from vdb_cache_names;
+--------------+----------+-------------+----------------------------------------------------------------+
| databaseName | viewName | vdbName | sqlName |
+--------------+----------+-------------+----------------------------------------------------------------+
| db01 | tab1 | C_db01_tab1 | C_TAB143241354201499808542805997242941910483330537268501054884 |
+--------------+----------+-------------+----------------------------------------------------------------+
truncate table tab1;
insert into tab1 values(1),(2),(3);
select * from tab1;
SELECT * FROM tab1
CONTEXT ( 'cache_preload' = 'true'
, 'cache_invalidate' = 'all_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'false')
;
select * from C_TAB143241354201499808542805997242941910483330537268501054884;
+------+----------------+-----------+
| col1 | expirationDate | rowStatus |
+------+----------------+-----------+
| 1 | 0 | V |
| 2 | 0 | V |
| 3 | 0 | V |
+------+----------------+-----------+
SELECT * FROM tab1
CONTEXT ( 'cache_preload' = 'true'
, 'cache_invalidate' = 'all_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'false')
;
select * from C_TAB143241354201499808542805997242941910483330537268501054884;
+------+----------------+-----------+
| col1 | expirationDate | rowStatus |
+------+----------------+-----------+
| 1 | 1692369431245 | I26 |
| 2 | 1692369431245 | I26 |
| 3 | 1692369431245 | I26 |
| 1 | 0 | V |
| 2 | 0 | V |
| 3 | 0 | V |
+------+----------------+-----------+
SELECT *
FROM tab1
where col1 > 1
CONTEXT (
'cache_preload' = 'true'
, 'cache_invalidate' = 'matching_rows'
, 'cache_wait_for_load' = 'true'
, 'cache_return_query_results' = 'false')
;
select * from C_TAB143241354201499808542805997242941910483330537268501054884;
+------+----------------+-----------+
| col1 | expirationDate | rowStatus |
+------+----------------+-----------+
| 1 | 1692369431245 | I26 |
| 2 | 1692369431245 | I26 |
| 3 | 1692369431245 | I26 |
| 1 | 0 | V |
| 2 | 1692369459285 | I28 |
| 3 | 1692369459285 | I28 |
| 2 | 0 | V |
| 3 | 0 | V |
+------+----------------+-----------+
rowStatus = V , expirationDate = 0 の行が有効な行
expirationDate > 0の場合は無効になったUNIXタイムが設定される
call CLEAN_CACHE_DATABASE('db01');
select * from C_TAB143241354201499808542805997242941910483330537268501054884;
+------+----------------+-----------+
| col1 | expirationDate | rowStatus |
+------+----------------+-----------+
| 1 | 0 | V |
| 2 | 0 | V |
| 3 | 0 | V |
+------+----------------+-----------+
