Creating Models › Target Servers for Physical Models › Database Property Editors › Oracle Physical Property Editors › Oracle Materialized View Editor

Oracle Materialized View Editor

Use the Oracle Materialized View 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.

Use the following options and tabs in the Oracle Materialized View Editor to define materialized view properties. You can open the Oracle Materialized View Editor by selecting Target - Oracle, Materialized View on the Model menu.

Previous

Positions the editor on the previous materialized view in the Navigation Grid.

Next

Positions the editor on the next materialized view in the Navigation Grid.

Sort

Sorts the materialized views by alphabetic or reverse alphabetic order. You select the method you want using the drop-down menu that opens after you click the Sort button. Visual cues are provided for how the materialized view list is currently sorted:

Specifies that the list is sorted in alphabetic order.

Specifies that the list is sorted in reverse alphabetic order.

New

Creates a new materialized view that becomes the current object in the editor.

Delete

Deletes the selected materialized view.

DB Sync

Opens the DB Sync wizard so you can perform a database level compare of objects in the current model with matching objects in another model.

Help

Opens online help for the editor.

Lets you filter a very large list of materialized views in the Navigation Grid to quickly locate the materialized view for which you need to view or define properties.

Name

Specifies the name of the materialized view. You can change the name of the materialized view in this field.

Owner

Lets you assign a database Owner to the materialized view.

Generate

Generates SQL during forward engineering. Clear the check box if you do not want to generate SQL.

Select

Lets you specify the tables, views, or materialized views to use to define the materialized view and create aliases and expressions.

From

Lets you specify the tables, views, and materialized views that you want to participate in the materialzied view.

Where

Lets you enter text to define a WHERE clause.

User Defined SQL

Lets you view and change a user-provided DDL statement.

SQL

Lets you view the SQL code used during Forward Engineering.

General

Lets you assign physical properties to a materialized view such as table compression and index properties.

Other Options

Lets you specify options such as cache type, parallel type, build type, using index, and query rewrite state.

Refresh

Lets you specify the refresh options for the materialized view.

LOB Storage

Lets you assign LOB storage parameters for the materialized view.

Style

Lets you work with style sheets, which are used to format the font and other graphical themes used in the display of the materialized view.

Icon

Lets you specify the icon style to use for the materialized view.

Synonym

Lets you add, modify, or delete synonyms for the materialized view.

Comment

Lets you enter any comments that you want to associate with the materialized view.

Object Creation Order

Lets you specify the order of DDL generation.

UDP

Lets you enter user-defined property values for a materialized view.

History

Lets you view the history details for the materialized view.

Notes

Lets you view materialized view history information and view or edit user notes.

Extended Notes

Lets you edit user notes or transfer notes to the Notes tab.