Goal

This document describes the tables and indexes created by the Denodo Cache system in a database using an Oracle 11g database as an example. This information can be used as a starting point to estimate the space that the cache database will use. For other Database Systems, the data types in the different tables might be different. It also depends on the cache database used as well as what kind of data is stored. The values below are just examples and therefore it might be that the column sizes differ from what is shown below.

Content

When configured, the cache system will create 4 metadata or control tables for the cache management in addition to 1 table for each cached view to store the cached data itself.

TABLES USED BY THE CACHE SYSTEM

1. The system creates a data table for each cached view (C_...).

This table will contain all the columns coming from the cached view plus two management columns:

Partial Cache:  

Full Cache:

For the data columns in the table different data types will be used depending on the original data types for those columns in the cached view.

Data Types mapping

Important: It is necessary to take into account that when the cache for a view/query expires the data in the cache is not deleted, just invalidated. So, data that is no longer valid will remain in the cache until the maintenance task actually removes the data.

2. Additional metadata tables for Cache management:

VDB_CACHE_NAMES

It has 1 row per cached view plus 1 row per column in the cached view that needs a different name in the cache server.

                Partial/Full:

VDB_CACHE_QUERYPATTERN

It has 1 row for each different query cached, including queries for the same view but with different conditions.

        VDB_CACHE_SEQUENCES

        1 row only

VDB_CACHE_VIEWNAME

1 row per cached view

DEFAULT INDEXES USED BY THE CACHE SYSTEM

Partial Mode

To improve the cache performance the cache system automatically creates some indexes on the metadata and data tables.

1. Indexes on data tables

For each data table an index will be created on the management columns for that

table: QUERYPATTERNID and ROWPOSITION.

2. Indexes on metadata tables

VDB_CACHE_NAMES

Indexes are created on columns: DATABASENAME and VIEWNAME.

VDB_CACHE_QUERYPATTERN

Indexes are created on columns: VDPCONDITIONLIST, NUMCONDITIONS, QPSTATUS, EXPIRATIONDATE, VDPCONDITION, QUERYPATTERNID, PROYECTEDFIELDS, DATABASENAME and VIEWNAME.

VDB_CACHE_SEQUENCES

No indexes

VDB_CACHE_VIEWNAME

Indexes are created on columns: DATABASENAME and VIEWNAME.

Full Mode

1. An index is created on the ROWSTATUS column when a view has a cache index marked as unique.
2. No other indexes are created on metadata tables.

Taking into account the tables needed by the cache database to store the data and the metadata, an estimate of the size needed by the database can be calculated. In addition to the space used by the metadata tables, the estimate will depend on the number of rows that will be cached for the different views (including the management columns) but should also take into account how long the invalid cached data will be kept in the cache.