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.

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.

Topics:

See Also:

Oracle Database Concepts for information about materialized views

Creating a Materialized View

To create a materialized view:

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

    Object Browser appears.

  2. Click Create.

  3. From the list of object types, select Materialized View.

  4. 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, which displays the SQL used to create the materialized view.

  5. Click Create Materialized View.

See Also:

"Building SQL Queries with Query Builder" and " Using SQL Commands "

Browsing a 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.

Summary of Available Views

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

Table 1-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.

  • 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. 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.

Dropping a Materialized View

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.