You can translate the document:

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:  

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

QUERYPATTERNID

NUMBER

38

ROWPOSITION

NUMBER

38

Full Cache:

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

EXPIRATIONDATE

NUMBER

38

ROWSTATUS

VARCHAR2

255

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

VDP

Cache

long,int

NUMBER(38)

float,double

FLOAT(126)

text

VARCHAR2(4000)

boolean

CHAR(1)

blob

BLOB

xml

CLOB

date (deprecated), timestampz

TIMESTAMP WITH TIMEZONE

localdate, time, timestamp

TIMESTAMP(6)

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.

  1. 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:

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

DATABASENAME

VARCHAR2

255

VIEWNAME

VARCHAR2

255

VDBNAME

VARCHAR2

255

SQLNAME

VARCHAR2

30

VDB_CACHE_QUERYPATTERN

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

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

QUERYPATTERNID

NUMBER

38

DATABASENAME

VARCHAR2

255

VIEWNAME

VARCHAR2

255

NUMCONDITIONS

NUMBER

38

VDPCONDITION

VARCHAR2

4000

VDPCONDITIONLIST

VARCHAR2

4000

PROJECTEDFIELDS

VARCHAR2

4000

EXPIRATIONDATE

NUMBER

38

QPSTATUS

CHAR

1

        VDB_CACHE_SEQUENCES

        1 row only

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

SEQUENCENAME

VARCHAR2

255

LOWERIDENTIFIER

NUMBER

38

BLOCKSIZE

NUMBER

38

VDB_CACHE_VIEWNAME

1 row per cached view

COLUMN_NAME

TYPE_NAME

COLUMN_SIZE

DATABASENAME

VARCHAR2

255

VIEWNAME

VARCHAR2

255

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.

  1. 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.

Disclaimer
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here