22 January, 2012

Refreshing an MV on a Prebuilt Table

I had earlier demonstrated creating an MV on a Prebuilt Table.  A few days ago, I had a question on refreshing such an MV.
Here is a simple demonstration :
SQL> -- create the source table
SQL> create table mv_source (id number not null, item_name varchar2(30));

Table created.

SQL> insert  into mv_source select object_id, object_name from user_objects;

6 rows created.

SQL> select count(*) from mv_source;

  COUNT(*)                                                                      
----------                                                                      
         6                                                                      

SQL> alter table mv_source add constraint mv_source_pk primary key (id);

Table altered.

SQL> 
SQL> -- create an MV Log on the source table
SQL> create materialized view log on mv_source;

Materialized view log created.

SQL> 
SQL> -- create the MV as a prebuilt table
SQL> -- e.g. this could have been via export/import ?
SQL> create table my_mv as select * from mv_source;

Table created.

SQL> create materialized view my_mv on prebuilt table refresh fast as select * from mv_source;

Materialized view created.

SQL> select count(*) from my_mv;

  COUNT(*)                                                                      
----------                                                                      
         6                                                                      

SQL> 
SQL> -- setup new data
SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> insert into mv_source values (1000000, 'AN_ITEM');

1 row created.

SQL> insert into mv_source values (2000000, 'ANOTHER_ITEM');

1 row created.

SQL> insert into mv_source values (3000000, 'THIRD_ITEM');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         3                                                                      

SQL> 
SQL> -- refresh the MV
SQL> exec dbms_session.session_trace_enable();

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MY_MV','F');

PL/SQL procedure successfully completed.

SQL> select count(*) from my_mv;

  COUNT(*)                                                                      
----------                                                                      
         9                                                                      

SQL> exec dbms_session.session_trace_disable();

PL/SQL procedure successfully completed.

SQL> select * from my_mv where id  > 999999 order by 1;

        ID ITEM_NAME                                                            
---------- ------------------------------                                       
   1000000 AN_ITEM                                                              
   2000000 ANOTHER_ITEM                                                         
   3000000 THIRD_ITEM                                                           

SQL> 
SQL> -- the Snapshot log on the source table has been cleared
SQL> select count(*) from mlog$_mv_source;

  COUNT(*)                                                                      
----------                                                                      
         0                                                                      

SQL> 
I started with the source table MV_SOURCE and then created MY_MV from this table.  New rows inserted into the source then populated the MV Log on the source.  A FAST REFRESH of the MV was executed as an Incremental Refresh rather than a full scan of the source table again !

Note : Here are some of the interesting SQLs from the trace of the Refresh call :

DELETE FROM "HEMANT"."MY_MV" SNAP$
WHERE
 "ID" IN (SELECT DISTINCT LOG$."ID" FROM (SELECT MLOG$."ID" FROM
  "HEMANT"."MLOG$_MV_SOURCE" MLOG$ WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" !=
   'I')) LOG$ WHERE (LOG$."ID") NOT IN (SELECT MAS_TAB$."ID" FROM "MV_SOURCE"
  "MAS_TAB$" WHERE LOG$."ID" = MAS_TAB$."ID"))


MERGE INTO "HEMANT"."MY_MV" "SNA$" USING (SELECT CURRENT$."ID",
  CURRENT$."ITEM_NAME" FROM (SELECT "MV_SOURCE"."ID" "ID",
  "MV_SOURCE"."ITEM_NAME" "ITEM_NAME" FROM "MV_SOURCE" "MV_SOURCE") CURRENT$,
  (SELECT DISTINCT MLOG$."ID" FROM "HEMANT"."MLOG$_MV_SOURCE" MLOG$ WHERE
  "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."ID" =
  LOG$."ID")"AV$" ON ("SNA$"."ID" = "AV$"."ID") WHEN MATCHED THEN UPDATE  SET
  "SNA$"."ID" = "AV$"."ID","SNA$"."ITEM_NAME" = "AV$"."ITEM_NAME" WHEN NOT
  MATCHED THEN INSERT  (SNA$."ID",SNA$."ITEM_NAME") VALUES (AV$."ID",
  AV$."ITEM_NAME")

Thus, it is possible to do a Fast Refresh on an MV that was defined on a Prebuilt Table.
.
.
.

No comments: