Materialized View Indexes

You can add multiple indexes to any column in the materialized view to better support the queries you plan to run.

It is more efficient to create all the indexes needed before you insert any data into your database. You can drop any of the optional indexes later if you decide you do not need them.

Indexes can greatly improve the performance of searches on the indexed columns. However, indexes take up space within the database and slow down insert, update, and delete operations. This section helps you determine when you should create an index and how to achieve maximum performance from your index.

There are many situations in which creating an index improves the performance of a database. An index provides an ordering of a table's rows based on the values in some or all of the columns. An index allows the database server to find rows quickly. It permits greater concurrency by limiting the number of database pages accessed. An index also affords the database server a convenient means of enforcing a uniqueness constraint on the rows in a table.

• Creating a Materialized View Index
  Create an index on a materialized view.
• Deleting a Materialized View Index
  Unused indexes take up space in your database. Delete a materialized view index if you do not require it.
• Rebuilding a Materialized View Index
  As data is modified in the underlying tables that the indexes operate on, the indexes become fragmented. Rebuild fragmented indexes on materialized views to improve performance.
• Validating a Materialized View Index
  Validate an index on a materialized view.
• Generating Materialized View Index DDL Commands
  Display the data description language SQL code for adding an index to a materialized view. The SQL code can be a useful reference and training tool.
• Viewing or Modifying Materialized View Index Properties
  Display and edit the properties for the selected materialized view index.
• Materialized View Index Privilege Summary
  A list of the system privileges and object permissions required to complete the various index tasks materialized views.