Managing Materialized Views

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base tables or in a different database.

Topics:

Creating a Materialized View
Browsing a Materialized View

Creating a Materialized View

To create a materialized view:

Navigate to Object Browser:
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Object Browser.
  
  Object Browser appears.
Click Create.
From the list of object types, select Materialized View.
Define the materialized view:
1. Materialized View Name - Enter a name.
2. Query - Specify a query to define the view.
  
  To access Query Builder or SQL Command Processor, click the appropriate link at the bottom of the page. The selected tool appears in a pop-up window. Once you generate the appropriate SQL, click Return to automatically close the popup window and return to the wizard with the SQL.
3. Click Next.
  
  A confirmation page appears. To view the SQL used to create the materialized view, click SQL.
Click Create.

Browsing a Materialized View

To view a materialized view:

Navigate to Object Browser:
1. Click the SQL Workshop icon on the Workspace home page.
2. Click Object Browser.
  
  Object Browser appears.
From the Object list, select Materialized Views.
From the Object Selection pane, select a view.

The Materialized View appears.

Summary of Available Views

Click the tabs at the top of the page to view different reports about the materialized view. Table 18-12 describes all available views.

Table 18-12 Available Views for Materialized View

View	Description
Materialized View	(Default) Displays details about the columns in the materialized view, including: Column Name Data type Nullable flag Default value Primary key Click Drop to delete the current materialized view.
Data	Displays a report of the data in the columns. Actions you can perform include: Query - Enables you to sort by column. To restrict specific rows, enter a condition in the Column Condition field. Use the percent sign (%) for wildcards. From Order by, select the columns you want to review and click Query. Count Rows - Displays a report of the data in the current table.
Details	Displays object details stored in `DBA_SNAPSHOTS` such as updatable and status.
Grants	Displays a list of grants on the current view, including grantee, privilege, and grant options. Actions you can perform in this view include Grant and Revoke.
Dependencies	Displays a list of objects that use (or depend) upon this materialized view.
SQL	Displays the SQL necessary to re-create this materialized view.