Indexing Materialized Views in Oracle

A materialized view is a physical table that stores the results of a query. Because it is a real segment on disk, you can (and usually should) create indexes on it, just as you would on a base table.

Indexing a materialized view is not just about speeding up queries. The right indexes also support:

• Query performance – faster access paths for reporting and analytics
• Fast refresh – efficient matching of changes from base tables to the materialized view
• Query rewrite – giving the optimizer cheap paths so it can safely choose the materialized view

This lesson explains when to index a materialized view, which index types to consider, and how those choices interact with refresh options in current Oracle Database releases (11gR2+, 19c, 23c).

You generally add indexes to a materialized view under three conditions:

1. Queries frequently filter or join on specific columns
   For example, if most reports filter by product_id, sales_month, or region_id, those columns are strong candidates for B-tree indexes.
2. Fast refresh is required
   FAST refresh relies on matching changed rows from materialized view logs to rows inside the materialized view. A unique or concatenated index on the materialized view key makes this lookup efficient.
3. Query rewrite is enabled
   When ENABLE QUERY REWRITE is used, the optimizer is more likely to choose the materialized view if it can see cheap index-based access paths for common query patterns.

If the materialized view is small, rarely queried, or refreshed very frequently, you may choose fewer or even no indexes to minimize refresh overhead.

1. B-tree indexes (default choice)

B-tree indexes are the standard indexing option and are the safest default for materialized views. They are appropriate when:

• The column has moderate or high cardinality (many distinct values)
• The column is used in WHERE predicates, joins, or ORDER BY clauses
• Refreshes occur regularly and you want predictable DML/index maintenance behavior

Typical B-tree indexes on a summary materialized view include:

• The grouping keys (for example, product_id, sales_month)
• Columns used in common filters (for example, region_id, channel_id)
• A unique or primary-key index on the logical key of the summary row

2. Bitmap indexes (data-warehouse scenarios)

Bitmap indexes are often used on materialized views in read-mostly data warehouses when:

• The column has low cardinality (few distinct values), such as status flags or a small set of regions
• Queries frequently combine predicates on multiple low-cardinality columns
• Refresh and DML activity is controlled (typically in batch windows)

Bitmap indexes can greatly accelerate star-schema style queries but are not recommended on materialized views with heavy concurrent DML or very frequent refreshes, because updates to bitmap keys can lock many rows and increase contention.

3. Function-based indexes

Materialized views often include expressions, for example:

• TRUNC(sales_date, 'MM') as a month key
• Derived currency or exchange-rate columns
• Case expressions that map raw values to dimensional attributes

If your queries filter on the same expressions, a function-based index on the materialized view is appropriate:

CREATE INDEX mv_sales_month_ix
    ON mv_sales_summary (TRUNC(sales_date, 'MM'));

This avoids recalculating the function for every row at query time and gives the optimizer a direct index path on the expression.

4. Local vs. global indexes on partitioned materialized views

In modern Oracle data warehouses, materialized views are frequently partitioned (for example, by month or day). In this case:

• Local indexes – one index segment per partition.
  These are easier to maintain with partition operations (add/drop/split/merge) and are usually preferred for large, partitioned materialized views.
• Global indexes – a single index spanning all partitions.
  Useful when queries routinely access many partitions at once, but more sensitive to partition maintenance, which may require index maintenance or rebuilds.

FAST refresh uses materialized view logs on the base tables to send only changed rows to the materialized view. Oracle then needs an efficient way to find the corresponding row(s) inside the materialized view.

1. Primary key–based vs ROWID-based materialized views

• WITH PRIMARY KEY
  The materialized view is joined back to base tables using primary keys. In this case, it is common (and often required) to have a unique or primary-key index on the materialized view’s logical key, which often matches the GROUP BY columns in summary views.
• WITH ROWID
  For join-only materialized views or certain advanced refresh scenarios, Oracle tracks base rows by ROWID. In these cases, you may see ROWID columns in the materialized view definition, and indexes on those ROWID columns can help locate rows during refresh.

2. Concatenated indexes on the summary key

For a typical aggregate materialized view, you will define a concatenated B-tree index on the summary key, for example:

CREATE UNIQUE INDEX sales_mv_pk
    ON sales_summary_mv (product_id, sales_month);

This index serves two purposes:

• It gives the refresh mechanism a fast way to locate and modify the correct summary row
• It speeds up analytic queries that group or filter by the same key

Indexing and refresh strategy are closely related. A simplified version of the REFRESH clause in CREATE MATERIALIZED VIEW looks like this:

REFRESH { FAST | COMPLETE | FORCE }
ON { DEMAND | COMMIT }

• FAST – uses materialized view logs and keys/ROWIDs to apply only incremental changes.
  Good indexing on the key columns is essential.
• COMPLETE – truncates and repopulates the materialized view by rerunning the defining query.
  Indexes are rebuilt or updated as the data is reinserted; many indexes can increase refresh time.
• FORCE – tries FAST; if not possible, falls back to COMPLETE.
• ON COMMIT – refreshes occur when changes are committed to the base table(s); indexing must be efficient enough to handle frequent refreshes.
• ON DEMAND – refresh is performed by a job or manual call (for example via DBMS_MVIEW.REFRESH), often during a batch window; you can afford heavier indexing here than with ON COMMIT.

Rule of thumb: the more often you refresh, the more cautious you should be about adding “nice-to-have” indexes that are not absolutely necessary for query or refresh performance.

The following example shows a simple monthly sales summary and a reasonable indexing strategy:

-- Base fact table: SALES_FACT
--   Columns: sales_date, product_id, region_id, sales_amount

CREATE MATERIALIZED VIEW sales_monthly_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE
AS
SELECT
    product_id,
    TRUNC(sales_date, 'MM') AS sales_month,
    SUM(sales_amount)       AS total_sales,
    COUNT(*)                AS txn_count
FROM sales_fact
GROUP BY
    product_id,
    TRUNC(sales_date, 'MM');

-- Index to support fast refresh and common queries
CREATE UNIQUE INDEX sales_monthly_mv_pk
    ON sales_monthly_mv (product_id, sales_month);

-- Optional: function-based index to support date-based filters
-- (if you often filter directly on TRUNC(sales_date, 'MM'))

In this case:

• The UNIQUE index on (product_id, sales_month) supports both fast refresh and analytic queries.
• Additional indexes should only be added if a specific query pattern demonstrates a clear benefit.

Most modern performance-tuning and data-warehouse designs use read-only materialized views:

• All DML occurs on the base tables
• Materialized views are refreshed on COMMIT or via scheduled jobs
• Indexes are tuned for query and refresh efficiency, not for local updates

Updatable materialized views (part of multimaster replication configurations) are still supported but are less common. Every additional index on an updatable materialized view increases the cost of propagating changes and resolving conflicts. Unless you specifically need bidirectional replication, a read-only, well-indexed materialized view is the simpler and more robust design.

In the next lesson, you will learn how to handle materialized views that become invalid due to changes in the underlying tables.

[1]Bitmapped Index: An index that uses bitmaps rather than row-by-row entries to represent key values. It is most effective for low-cardinality columns in read-mostly workloads and is generally avoided on OLTP-style, highly updated data.