Search My Oracle Blog

Custom Search

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:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016