Physical Modeling › Oracle Physical Property Editors › Oracle Materialized Views Editor

Oracle Materialized Views Editor

Use the Oracle Materialized Views Editor to create or edit materialized views.

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 be used to 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:

• 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, and it is stored in 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, it 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.

You can open the Oracle Materialized Views Editor in one of the following ways:

• Select Materialized Views from the Model menu
• Right-click a materialized view in the diagram and select Database Materialized View Properties from the menu and select a property that you want to modify
• Right-click a materialized view in the Model Explorer and select Properties
• Single-click a materialized view in the Model Explorer and click the Property Editor icon

The Oracle Materialized Views Editor dialog includes the following tabs:

Select Add tables, views, and materialized views to a materialized view column and create aliases and expressions.

From Specify the source tables, view, or materialized views.

Where Specify search conditions and criteria to use when you generate the SQL code for a materialized view.

General Assign physical properties to a materialized view such as table compression and index properties. The options available vary by Oracle version.

Other Options Select options such as cache type, parallel type, build type, using index, and query rewrite state.

Refresh

Select the refresh options for a materialized view. The options available vary between Oracle 9i and Oracle 10g.

Partitions Select the type of partitioning for a materialized view.

Synonym Add, modify, or delete synonyms for a materialized view.

LOB Storage Assign LOB storage parameters for a materialized view.

SQL View and change the SQL code that is maintained for a materialized view.

Comment Add or edit comments for a materialized view.

UDP Enter user-defined property values for a materialized view.

The following describes all of the other options available in this dialog:

Materialized View

A drop-down list box that contains the materialized views in the current model.

Name

To rename the materialized view, enter a different name in the Name text box.

Owner

You can enter a name in this text box to assign a database Owner to the selected materialized view.

Generate

Select this check box if you want to generate DDL for this object during Forward Engineering. If you do not select the check box, no DDL will be generated for this object during Forward Engineering.

DB Sync

Starts the Complete Compare task so you can synchronize the physical objects defined in your model with the information stored on the server.