Physical Modeling › Materialized Views

Materialized Views

In a physical model based on Oracle, you can create a materialized view, which is a view that stores the result set of a query against any number of other materialized views, views, and tables. Materialized views can improve the performance of queries.

To the database user, a materialized view looks like a real table with a set of named columns and rows of data. However, a materialized view is not a permanently stored set of data values. The rows and columns of data visible through the materialized view are the result of the database query that defines the materialized view.

You can use materialized views to do the following:

• Create a customized presentation of the data stored in a database.
• Restrict access to data, allowing different users access to only certain rows or columns of a materialized view.
• Simplify database access by creating customized structures tailored to the needs of individual users. A user can write a simple query on a materialized view rather than a complex query on the actual database tables.

Materialized views are supported in the following features:

Forward Engineering

When you forward-engineer your data model to generate a database, the SQL code to define the materialized view is generated to the database.

Reverse Engineering

When you reverse-engineer an existing database that includes one or more materialized views, each materialized view is imported, the syntax is parsed, and where possible the relationships to the tables referenced by the materialized view are created.

Complete Compare

When you use complete compare and update a materialized view either in the model or in the database, you can keep the model materialized view specification in sync with the database table specification.

When you add a materialized view to a model, the materialized view is represented as a box with rounded corners. A relationship between a table and a materialized view indicates that the materialized view table references one or more of the columns from that table.

A materialized view column can be a reference to a table column or a user-defined expression.

• For materialized view columns that are references to a table column, the following naming convention is used:
  <MaterializedViewColumnName>: <SourceTableName>.<SourceColumnName>
• For materialized view columns that are user-defined expressions, the following naming convention is used:
  <MaterializedViewColumnName >: <expression>

After you draw a view relationship between a table and a materialized view, you must use on-diagram editing or the Views editor to migrate columns in the table to the materialized view. By default, the <MaterializedViewColumnName> is the same as the <SourceColumnName> until you edit the materialized view column name.

You can edit materialized views directly in the diagram window using on-diagram editing. When you drag a column from a table into a materialized view, the materialized view column and the relationship are created.

If you delete a table column that is referenced by a materialized view, the corresponding materialized view column is deleted. If you delete a table that is referenced by a materialized view, the corresponding materialized view columns are deleted.