Views, materialized views and indexes

• A view is a saved query with a name. Query it like a table; it runs the query each time.
• A materialized view saves the query's result, and refreshes it when you say so.
• An index is a sorted copy of some columns that lets Oracle find rows without reading the whole table.

The test case

A season batting table that analysts can query by name: one row per batter per season with runs, balls, fours and sixes. Show the 2026 top five from it.

Setup

begin
  for v in (select mview_name from user_mviews where mview_name = 'D103_BAT_MV') loop
    execute immediate 'drop materialized view d103_bat_mv';
  end loop;
  for t in (select table_name from user_tables where table_name = 'D103_BAT_T') loop
    execute immediate 'drop table d103_bat_t purge';
  end loop;
end;
/

Expected result

Way 1: a view

Setup

create or replace view d103_bat_v as
select b.season, p.player_name, sum(b.runs_batter) as runs,
       sum(case when b.extra_type = 'WD' then 0 else 1 end) as balls,
       sum(case when b.runs_batter = 4 then 1 else 0 end) as fours,
       sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
group  by b.season, p.player_name;

select player_name, runs, balls, fours, sixes
from   d103_bat_v
where  season = 2026
order  by runs desc
fetch  first 5 rows only;

Always up to date, costs nothing to store, and the query runs every time someone asks. (CR_BALLS_V, which every page uses, is itself a view on CR_DELIVERIES.)

Way 2: a materialized view

Setup

create materialized view d103_bat_mv
build immediate refresh complete on demand as
select b.season, p.player_name, sum(b.runs_batter) as runs,
       sum(case when b.extra_type = 'WD' then 0 else 1 end) as balls,
       sum(case when b.runs_batter = 4 then 1 else 0 end) as fours,
       sum(case when b.runs_batter = 6 then 1 else 0 end) as sixes
from   cr_balls_v b
join   cr_players p on p.player_id = b.batter_id
group  by b.season, p.player_name;

select player_name, runs, balls, fours, sixes
from   d103_bat_mv
where  season = 2026
order  by runs desc
fetch  first 5 rows only;

The result is stored, so reading it is fast, but it only changes when refreshed: exec dbms_mview.refresh('D103_BAT_MV'). Good for dashboards over big data that changes nightly.

Way 3: a summary table (CTAS) with an index

Setup

create table d103_bat_t as select * from d103_bat_v;

Setup

create index d103_bat_t_ix on d103_bat_t (season, runs);

select player_name, runs, balls, fours, sixes
from   d103_bat_t
where  season = 2026
order  by runs desc
fetch  first 5 rows only;

A snapshot you manage yourself. The index on (season, runs) is exactly what this query wants: Oracle can jump to season 2026 and read it in runs order, so it may not need to sort at all (Part 14 shows how to check).

Indexes in one minute

create index cr_del_batter_ix on cr_deliveries (batter_id);           -- the everyday kind (B-tree)
create unique index d103_uk on d103_bat_t (season, player_name);       -- also enforces uniqueness
create index cr_players_upper_ix on cr_players (upper(player_name));   -- function-based: for upper(...) = ...
create bitmap index cr_del_extra_bx on cr_deliveries (extra_type);     -- few values, mostly read (warehouses)

Indexes speed up reads and slow down writes (every insert updates every index). Index the columns you search and join on, not every column you have.

19c and 26ai

The same on both.

Gotchas

• A view doesn't store data, so a slow query is just as slow through a view. Views are for naming and hiding complexity, not speed.
• ORDER BY inside a view isn't guaranteed to survive whatever query uses it. Sort in the outer query.
• Indexes aren't used for everything: WHERE upper(player_name) = ... can't use a plain index on player_name, and a query that reads half the table is faster without one.