3.13 Managing Materialized Views

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. You can create, browse, drop a materialized view and view report.

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.

Materialized views are often used to improve performance, especially when storing data locally that is sourced from tables or views using a database link. Similarly, materialized views are often used instead of views when there are extensive calculations or summarizations, especially on particularly large data sets. Materialized views are refreshed at specified intervals which means the data is not always up-to-date but queries against them are significantly faster for the examples given above.

• Creating a Materialized View
  Create a materialized view using Object Browser.
• Browsing a Materialized View
  Select a materialized view from the Object Selection pane and view different reports about the materialized view.
• Reports for Materialized Views
  Alternative views available when viewing a materialized view in Object Browser.
• Dropping a Materialized View
  Select a materialized view from the Object Selection pane and click Drop.

See Also:

"Oracle Database Concepts" for information about materialized views

3.13.1 Creating a Materialized View

Create a materialized view using Object Browser.

To create a materialized view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. Click the Create icon.
3. From the list of object types, select Materialized View.
4. For Define:

   1. Materialized View Name - Enter the name of the view. View names must conform to Oracle naming conventions and cannot contain spaces, or start with a number or underscore.

      To preserve the case of the name you enter, click Preserve Case.

   2. Query - Specify a query to define the view.

      Tip:

      To access Query Builder or SQL Commands, click the appropriate button at the bottom of the page. The selected tool displays in a pop-up window. Once you create 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, which displays the SQL used to create the materialized view.

5. Click Create Materialized View.

See Also:

• "Using Query Builder"

• "Using SQL Commands"

3.13.2 Browsing a Materialized View

Select a materialized view from the Object Selection pane and view different reports about the materialized view.

To view a materialized view:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Materialized Views.
3. From the Object Selection pane, select a view.

   The Materialized View appears.

4. Click the tabs at the top of the page to view different reports about the materialized view.

3.13.3 Reports for Materialized Views

Alternative views available when viewing a materialized view in Object Browser.

Table 3-12 describes all available reports for materialized views.

Table 3-12 Available Reports for Materialized Views

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. Download - Click this link to export the data as a comma-delimited file (.csv) file.
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. You can perform Grant and Revoke in this view.
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.

3.13.4 Dropping a Materialized View

Select a materialized view from the Object Selection pane and click Drop.

To drop a materialized view in Object Browser:

1. On the Workspace home page, click SQL Workshop and then Object Browser.

   Object Browser appears.

2. From the Object list, select Materialized Views.
3. From the Object Selection pane, select a view.

   The Materialized View appears.

4. Click Drop.
5. To confirm, click Finish.