Re: Nested Materialized View Refresh

From: ddf <oratune_at_msn.com>
Date: Fri, 12 Jun 2009 07:06:44 -0700 (PDT)
Message-ID: <fff8dcd9-395a-4d46-889a-9e30ce42beb2_at_x1g2000prh.googlegroups.com>

On Jun 12, 5:00 am, Veeru71 <m_ad..._at_hotmail.com> wrote:
> Hi
> I have the following nested materialized view (MV based on another
> MV)......
>
> SERVER1
> ---------------
> create table TEST (a number(5) primary key not null, b varchar2(30))
>
> SERVER2
> ----------------
> create materialized view TEST_MV1
> refresh complete on demand with primary key
> as select a, b from TEST_at_SERVER1
>
> SERVER3
> ---------------
> create materialized view TEST_MV2
> refresh complete on demand with primary key
> as select a, b from TEST_MV1_at_SERVER2
>
> -- to refresh on Server-3
> begin
> dbms_mview.refresh('TEST_MV2', 'C', nested=>TRUE)
> end;
>
> When I refresh TEST_MV2 with  'nested=>TRUE' option as above, my
> understanding is it is supposed to refresh TEST_MV1 first (from the
> base table - TEST)  and then it should refresh TEST_MV2. Apparently it
> is doing nothing. It is not even pulling changes from Server-2 to
> Server-3, leave alone pulling changes from Server-1 to Server-2.
>
> When I refresh TEST_MV2 without 'nested=>TRUE'  parameter,  it is just
> refreshing TEST_MV2 which is fine.
>
> What am I missing here with "nested refresh" concept ?
>
> Thanks.

A materialized view log on TEST_MV1, for starters according to the documentation, and nested materialized views are, to my knowledge, defined on the same server, not across database links. Let's try this with, and without, a materialized view log and see if the nested refresh succeeds:

--
-- With materialized view log on 'parent' materialized view
--
SQL> create table TEST (a number(5) primary key not null, b varchar2
(30));

Table created.

SQL>
SQL> create materialized view TEST_MV1
  2  refresh complete on demand with primary key
  3  as select a, b from TEST;

Materialized view created.

SQL>
SQL> select a, b
  2  from test_mv1;

no rows selected

SQL>
SQL> create materialized view log on test_mv1
  2  with primary key
  3  including new values;

Materialized view log created.

SQL>
SQL> create materialized view TEST_MV2
  2  refresh complete on demand with primary key
  3  as select a, b from TEST_MV1;

Materialized view created.

SQL>
SQL> select a, b
  2  from test_mv2;

no rows selected

SQL>
SQL> begin
  2          for i in 1..10 loop
  3                  insert into test
  4                  values(i, 'Yargenplotz '||i);
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin
  2          dbms_mview.refresh('TEST_MV2', 'C', nested=>TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select a, b
  2  from test_mv1;

         A B
---------- ------------------------------
         1 Yargenplotz 1
         2 Yargenplotz 2
         3 Yargenplotz 3
         4 Yargenplotz 4
         5 Yargenplotz 5
         6 Yargenplotz 6
         7 Yargenplotz 7
         8 Yargenplotz 8
         9 Yargenplotz 9
        10 Yargenplotz 10

10 rows selected.

SQL>
SQL> select a, b
  2  from test_mv2;

         A B
---------- ------------------------------
         1 Yargenplotz 1
         2 Yargenplotz 2
         3 Yargenplotz 3
         4 Yargenplotz 4
         5 Yargenplotz 5
         6 Yargenplotz 6
         7 Yargenplotz 7
         8 Yargenplotz 8
         9 Yargenplotz 9
        10 Yargenplotz 10

10 rows selected.

SQL>

--
-- Without materialized view log on 'parent' materialized view
--
SQL> create table TEST (a number(5) primary key not null, b varchar2
(30));

Table created.

SQL>
SQL> create materialized view TEST_MV1
  2  refresh complete on demand with primary key
  3  as select a, b from TEST;

Materialized view created.

SQL>
SQL> select a, b
  2  from test_mv1;

no rows selected

SQL>
SQL> create materialized view TEST_MV2
  2  refresh complete on demand with primary key
  3  as select a, b from TEST_MV1;

Materialized view created.

SQL>
SQL> select a, b
  2  from test_mv2;

no rows selected

SQL>
SQL> begin
  2          for i in 1..10 loop
  3                  insert into test
  4                  values(i, 'Yargenplotz '||i);
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> begin
  2          dbms_mview.refresh('TEST_MV2', 'C', nested=>TRUE);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select a, b
  2  from test_mv1;

         A B
---------- ------------------------------
         1 Yargenplotz 1
         2 Yargenplotz 2
         3 Yargenplotz 3
         4 Yargenplotz 4
         5 Yargenplotz 5
         6 Yargenplotz 6
         7 Yargenplotz 7
         8 Yargenplotz 8
         9 Yargenplotz 9
        10 Yargenplotz 10

10 rows selected.

SQL>
SQL> select a, b
  2  from test_mv2;

         A B
---------- ------------------------------
         1 Yargenplotz 1
         2 Yargenplotz 2
         3 Yargenplotz 3
         4 Yargenplotz 4
         5 Yargenplotz 5
         6 Yargenplotz 6
         7 Yargenplotz 7
         8 Yargenplotz 8
         9 Yargenplotz 9
        10 Yargenplotz 10

10 rows selected.

SQL>

So we find that the materiiallized view log on the 'parent'
materialized view is not necessary, but the 'nested' materialized
views must be created on the same server, not across database links,
because the dependency tree doesn't extend across servers.  Your own
tests prove this.


David Fitzjarrell

Received on Fri Jun 12 2009 - 09:06:44 CDT