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

I will also define a row as “marked in the log” if it has a match in the set “select rid$ from mlog$_test_inner” – the matching column being test_inner_rowid for the MV and the actual rowid for the inner table test_inner.

I am covering only 11.2.0.3 in the same scenario of the inner join post quoted above: MV logs configured to “log everything” and a single fast refresh propagating all possible types of regular (no direct-path) INSERTs, UPDATEs and DELETEs performed on the master tables (I haven’t investigated possible variants of the refresh algorithm if only a subset of those DML types is performed).

The DEL macro step with the unique constraint

As stated above , it consists of a simple update:

/* MV_REFRESH (UPD) */

update test_mv

set jinner = null, xinner = null, pkinner = null, test_inner_rowid = null

where test_inner_rowid in (select rid$ from mlog$_test_inner)

that simply flips to null the columns coming from the inner table of all marked rows of the MV.

The INS macro step with the unique constraint

Again, as stated above, this step consists of a (not so simple) update:

/* MV_REFRESH (UPD) */

update /*+ bypass_ujvc */ (

select test_mv.jinner target_0, jv.jinner source_0,

test_mv.xinner target_1, jv.xinner source_1,

test_mv.pkinner target_2, jv.pkinner source_2,

test_mv.test_inner_rowid target_3, jv.rid$ source_3

from ( select test_inner.rowid rid$, test_inner.*

from test_inner

where rowid in (select rid$ from mlog$_test_inner)

) jv,

test_outer,

test_mv

where test_outer.jouter = jv.jinner

and test_mv.test_outer_rowid = test_outer.rowid

)

set target_0 = source_0,

target_1 = source_1,

target_2 = source_2,

target_3 = source_3

this statement joins the marked rows of the inner table with the outer table (using an inner join, not an outer join, of course) and then looks for matching slices (by test_outer_rowid) in the MV; for every match, it flips the columns coming from the inner table from null to their actual values.

As a side note, it’s worth noting that the statement updates an updatable in-line view, which is actually “tagged as updatable” by the hint “bypass_ujvc” (“bypass the Updatable Join Validity Check” probably), an hint that only Oracle code can use nowadays.

speeding up

Looking at the above SQL statements, it comes out naturally that if your fast refresh process propagates a small number of modifications, it is beneficial, to speed up the fast refresh from the inner table, to create

- for the DEL step: an index on test_mv (test_inner_rowid)

- for the INS step: an index on test_mv(test_outer_rowid) and another on test_outer(jouter).

To also speed up the refresh from the outer table (not shown in this post), you would also create an index on on test_inner(jinner) and test_mv(test_outer_rowid).

So in essence, the very same indexes as in the inner join case need to be created for the outer join. But note that if you never propagate from the outer table, the index on test_mv(test_outer_rowid) has to be created anyway – that index was not necessary in the inner join case.

Of course, as the number of modifications increase, and/or if you fast-refresh in parallel, the indexes might not be used by the CBO that could prefer full-scanning the tables; in that case they would just be update overhead. Your mileage may vary, as always – but knowing the actual algorithm and SQL submitted is for sure very helpful to decide. Hope this helps.

Page 2 of 2 | Previous page

2 comments on this post.
  1. Latest Data News, Oracle, MySQL, SQL Server, BigData:

    [...] Alberto has a good post on fast refresh of outer-join-only materialized views. [...]

  2. Alberto Dell’Era’s Oracle blog » fast refresh of outer-join-only materialized views – algorithm, part 2:

    [...] this post, we are going to complete part 1 illustrating the (considerably more complex) general case of a fast refresh from a master inner [...]

Leave a comment