fast refresh of join-only materialized views – algorithm summary
This post investigates how Oracle fast refreshes materialized views containing only joins of master tables:
[sql]
create materialized view test_mv
build immediate
refresh fast on demand
as
select test_t1.*, test_t1.rowid as test_t1_rowid,
test_t2.*, test_t2.rowid as test_t2_rowid,
test_t3.*, test_t3.rowid as test_t3_rowid
from test_t1, test_t2, test_t3
where test_t1.j1_2 = test_t2.j2_1
and test_t2.j2_3 = test_t3.j3_2
;
[/sql]
The fast refresh algorithm is simple and very easy to understand – so trivial in fact that once examined and understood, the possible tuning techniques follow naturally.…