Currently browsing

April 2013

fast refresh of outer-join-only materialized views – algorithm, part 2

In this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner table without a unique constraint on the joined column(s).

To recap, now the outer slice can be composed of more than one row, for example:

ooo inn1
ooo inn2

and hence, both the DEL and INS step must consider (and read) the whole outer slice even if only a subset of the inner rows have been modified.…

fast refresh of outer-join-only materialized views – algorithm, part 1

In this series of posts we will discuss how Oracle refreshes materialized views (MV) containing only OUTER joins, covering only 11.2.0.3. We will use the very same scenario (MV log configuration, DML type, etc) as in the inner join case, “just” turning the inner join into an outer join:

create materialized view test_mv
build immediate
refresh fast on demand
as
select test_outer.*, test_outer.rowid as test_outer_rowid,
       test_inner.*, test_inner.rowid as test_inner_rowid
  from test_outer, test_inner
 where test_outer.jouter = test_inner.jinner(+)
; 

For the outer case, the overall strategy is the same we already saw for inner joins: modifications from each master table are propagated separately to the MV, and by still performing, for each master table, the same two macro steps (first the DEL, and then the INS one).…

OLTP compression: migrated rows are compressed

In his articles Compression in Oracle – Part 2: Read-Only Data and Compression in Oracle – Part 3: OLTP Compression, Jonathan Lewis has shown that block (re)compression is never attempted on updates – it is attempted only on inserts (and, of course, only if the used space crosses the PCTFREE threshold).
Now, since a row migration could be considered a row re-insertion – does it trigger a compression attempt of the block that the row is migrated into?…

Bitnami