Cache database size estimate

Applies to: Denodo 8.0 , Denodo 7.0 , Denodo 6.0
Last modified on: 28 May 2020
Tags: Administration Cache

Download document

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

Denodo 7.0 and 8.0:

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)

Denodo 6.0:

VDP

Cache

long,int

NUMBER(38)

float,double

FLOAT(126)

text

VARCHAR2(4000)

boolean

CHAR(1)

blob

BLOB

xml

CLOB

date

TIMESTAMP WITH TIMEZONE

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

PROYECTEDFIELDS

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

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.

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.

Questions

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

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training