2 Replies Latest reply: Jul 16, 2013 2:23 AM by grygorii RSS

    Refresh materialized view via parallel pipelined function

    grygorii

      I try to perform refresh materialized view via parallel pipelined function:

      SELECT * FROM

      table(parallel_refresh(cursor (select /*+ parallel(t) */ * from list_of_matview t)));

       

      query1 -> mv1 -> mv2

      where:

      query1 - underlying query for mat.view mv1

      mv1  - materialized view on query1

      mv2  - materialized view on mv1

       

      mv1 and view log on it are refreshed, but when try to perform fast refresh on mv2 then nothing happens - neither errors nor updates.

       

      Scripts:

      drop materialized view mv_tbl1;

      drop materialized view mv_tbl2;

      drop materialized view mv_tbl_next_level;

      drop table mv_tbl_next_level;

      drop table tbl2;

      drop table tbl1;

      drop table list_tbl;

      drop function parallel_refresh;

      drop type a_test_1;

      drop type a_test;

      ----------------------------

      create table list_tbl

      (mvname varchar2(10),fake char(100) )

      pctfree 99;

      insert into list_tbl(mvname,fake) values('MV_TBL1',' ');

      insert into list_tbl(mvname,fake) values('MV_TBL2',' ');

      commit;

      ----------------------------

      create table tbl1

      as

      SELECT rownum rn,'level'||level lname,level lvl FROM dual connect by level < 3;

      alter table tbl1 add constraint tbl1_pk primary key(rn) using index;

      create materialized view log on tbl1 with rowid,primary key;

       

      create table tbl2

      as

      SELECT rownum rn,'level'||level lname,level lvl FROM dual connect by level < 3;

      alter table tbl2 add constraint tbl2_pk primary key(rn) using index;

      create materialized view log on tbl2 with rowid,primary key;

      ----------------------------

      create materialized view MV_TBL1

      nologging

      build deferred

      refresh fast on demand

      as

      SELECT * FROM tbl1;

      create materialized view log on mv_tbl1 with rowid,primary key;

       

      create materialized view MV_TBL2

      nologging

      build deferred

      refresh fast on demand

      as

      SELECT * FROM tbl2;

      create materialized view log on mv_tbl2 with rowid,primary key;

      ----------------------------

      create table mv_tbl_next_level

      as

      SELECT * FROM mv_tbl1 where 1=0;

      create materialized view mv_tbl_next_level

      on prebuilt table

      refresh fast on demand

      as

      SELECT * FROM mv_tbl1;

      ----------------------------

      create or replace type a_test as object

      (

        id number,

        mvname varchar2(30)

      )

      ;

      create or replace type a_test_1 as table of a_test;

      ----------------------------

      create or replace function parallel_refresh(l_cursor in sys_refcursor)

        return a_test_1

        PARALLEL_ENABLE(PARTITION l_cursor BY ANY)

        PIPELINED IS

        mv_rc list_tbl%rowtype;

        l_session_id number;

        pragma autonomous_transaction;

      begin

        select sid into l_session_id from v$mystat where rownum = 1;

        loop

         fetch l_cursor

         into mv_rc;

         exit when l_cursor%notfound;

        dbms_mview.refresh(mv_rc.mvname, 'f');

         pipe row(a_test(l_session_id, mv_rc.mvname));

        end loop;

        close l_cursor;

        RETURN;

      end;

      ------------------

       

      Perform simple refresh:

       

      -- do complete refresh

      begin

        dbms_mview.refresh('mv_tbl1','c');

        dbms_mview.refresh('mv_tbl_next_level','c');

      end;

       

      ----------------------------

      begin

        -- update value

        update tbl1 a set a.lvl = a.lvl + 1;

        commit;

        -- do fast refresh

        dbms_mview.refresh('mv_tbl1', 'f');

        dbms_mview.refresh('mv_tbl_next_level', 'f');

      end;

      ----------------------------

      -- check result

      SELECT (SELECT a.lvl FROM mv_tbl1 a where a.rn = 1) mv_tbl1,

        (SELECT a.lvl FROM mv_tbl_next_level a where a.rn = 1) mv_tbl_next_level

        FROM dual;

       

      Everything is Ok

       

      -------------------

      Perform refresh via parallel pipelined function

      -- update value

      update tbl1 a set a.lvl = a.lvl + 1;

      commit;

      -- do fast refresh via parallel pipelined

      SELECTFROM table(parallel_refresh(cursor (select /*+ parallel(t1) */  *   from list_tbl t1)));

       

      -- do fast refresh mv_tbl_next_level

      begin

        dbms_mview.refresh('mv_tbl_next_level', 'f');

      end;

      -- check result

      SELECT (SELECT a.lvl FROM mv_tbl1 a where a.rn = 1) mv_tbl1,

        (SELECT a.lvl FROM mv_tbl_next_level a where a.rn = 1) mv_tbl_next_level

        FROM dual;

       

      mv_tbl_next_level is not been refreshed.

       

      Who knows what is the problem?