Data Vault Administrator Guide

6.5
- 6.5.1
- 6.5 HotFix 1
- 6.4.4
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Materialized Views

You can create, refresh, and drop materialized views in the Data Vault. Materialized views improve the performance of queries that have multiple joins or aggregations.

A materialized view is a database object in the Data Vault that contains the results of an SQL query. When you create a materialized view, the Data Vault creates and registers the materialized view as a table. The Data Vault also registers the tables referenced in the SQL query of the materialized view. After you create the materialized view, you can include the materialized view in a SELECT statement.

You cannot apply legal hold, retention, data browse, or data purge functionality to materialized views. Compliance features are supported only on the retired tables themselves, and not the overlying materialized views. When you purge data from a table, you must manually refresh the materialized views in addition to the indexes to sync the materialized view with the table.

You cannot delete the tables that are referenced in a materialized view. If you update the tables that are referenced in the materialized view, you must refresh the materialized view to access the updated data.

When you create a materialized view, you cannot have a trailing space at the end of the alias column names in the query used to create the materialized view.

When you delete a materialized view, the Data Vault unregisters the data files that are associated with the materialized view. The Data Vault also deletes the materialized view table from the system catalog.

Materialized views in the Data Vault differ from the virtual views supported by Data Archive.

For information about the materialized views used by the Application Retirement for Healthcare Accelerator, see the

Informatica Data Archive Application Retirement for Healthcare Accelerator Reference

Location Parameter

To create a materialized view, use the argument MVIEWDIR as the location where the materialized view is created.

If you do not specify the MVIEWDIR location as shown in the examples below, Data Vault creates the materialized view in the SHAREDIR location as provided in the

ssa.ini

file. Data Vault housekeeping activities can clear the SHAREDIR location at any time. If Data Vault clears the SHAREDIR location, you can lose any materialized views you created previously. As a best practice, use the MVIEWDIR location in either the

ssa.ini

file or in the SQL command itself.

Specify the MVIEWDIR location in one of the following ways:

As an parameter in the

ssa.ini

file.

Add the MVIEWDIR parameter in the [QUERY] and [SERVER] sections of the

ssa.ini

file. For example:

[QUERY]

………………

MVIEWDIR=/data/mviewdir

[SERVER]

…………….

MVIEWDIR=/data/mviewdir

After you add the entries to the

ssa.ini

file, restart Data Vault.

Alternatively, you can specify MVIEWDIR as part of the SQL to create the materialized view itself. With this method you do not need to restart Data Vault. For example:

create materialized view <view name> as SELECT <Col1 or expression 1>, <Col2 or expression 2> …. < Coln or expression n> FROM <table name> WHERE <condition>
{GROUP BY} {ORDER BY} {Limit}
MVIEWDIR '/data/mviewdir'

In the example above,

/data/mviewdir

is just an example. You can provide any location other than the SHAREDIR or TEMP folders.