3.13 Managing Materialized Views

A materialized view is a query result that is stored or materialized in advance as a schema object. Use Object Browser to view, or drop materialized views.

Materialized views summarize, compute, replicate, and distribute data. A materialized view often serves as a master table in replication and a fact table in data warehousing. 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.

• Browsing a Materialized View
  Select a materialized view from the Object Tree and access tabs in the Object Detail View.
• Dropping a Materialized View
  Select a materialized view from the Object Tree and click Drop.

See Also:

Overview of Materialized Views in Oracle Database Concepts

3.13.1 Browsing a Materialized View

Select a materialized view from the Object Tree and access tabs in the Object Detail View.

To browse a materialized view:

1. In Object Browser, Object Tree, expand Materialized View and select a materialized view.
2. The Object Detail View appears and displays the following tabs:
   • Columns - Displays the columns in the current materialized view, including the Column Name, Data Type, Nullable flag, Default value, Primary Key, and Comment. Available actions include:
     • Comment
     • Drop
     • Refresh
   • Data - Displays the data in the columns. Available actions include:
     • Columns - Configure the columns to display.
     • Filter - Create a filter by selecting a column, operation, and value.
     • Count Rows - Displays the number of rows in the current view.
     • Download - Download an XLSX file.
     • Refresh - Refresh the current view.
   • Details - Displays object details stored in DBA_SNAPSHOTS such as updatable and status.
   • Grants - Displays grants on the current view, including privilege, grantee, grantable, grantor, and object name. Available actions include:
     • Grant
     • Revoke
     • Refresh
   • Dependencies - Displays a list of objects that use (or depend) upon this materialized view. Available actions include:
     • Refresh
   • DDL - Displays the DDL necessary to re-create the materialized view.

     Available actions include:

     • Download - Download an XLSX file.
     • Refresh - Refresh the current view.

3.13.2 Dropping a Materialized View

Select a materialized view from the Object Tree and click Drop.

To drop a materialized view:

1. In Object Browser, Object Tree, expand Materialized View and select a materialized view.
2. Click the Columns tab and then Drop.

   In the Drop dialog:

   1. Drop - Review the details.
   2. SQL - Displays the SQL generated to drop the database link. To copy the displayed SQL, click the Copy icon.
   3. To confirm your selection, click Drop again.