You Asked

Hello Masters :-),

I read the documentation about fast refreshable materialized views. It states:

"The changes for conventional DML changes are stored in the materialized view log associated with the master table. The changes for direct-path INSERT operations are stored in the direct loader log."

I checked this behavior with the following test case:

drop table t_direct_path;
drop materialized view mv_direct_path ;

create table t_direct_path ( nu number(5) primary key);

create materialized view log on t_direct_path ;

create materialized view mv_direct_path 
refresh fast on demand as 
select * from t_direct_path;

insert into t_direct_path values( 10000);
insert into t_direct_path values( 20000);
insert into t_direct_path values( 30000);
insert into t_direct_path values( 40000);
insert into t_direct_path values( 50000);
commit;

select * from t_direct_path;
select * from mlog$_t_direct_path;

--those records are not visible in the MV log!
insert /*+ append*/ into t_direct_path 
(select level from dual connect by level < 20); 
commit;

select * from t_direct_path;
--still 5 rows
select * from mlog$_t_direct_path;

execute dbms_mview.refresh('MV_DIRECT_PATH', 'F');

SELECT * FROM mv_direct_path;

I was wondering how exactly the additional 19 rows of the direct path load have been successfully transferred to the MV? Is there any option (a data dict view, a log file or something like the MLOG$ object) where I would be able to see how many rows have been inserted into the master table by direct path loads?

THANKS IN ADVANCE! YOU ARE AN AMAZING TEAM!
Jonas

and Connor said...

"ALL_SUMDELTA" is the missing piece of the puzzle here, available from 11.2.0.2 onwards. Your (excellent) example reproduced below


SQL> drop table t_direct_path purge;

Table dropped.

SQL> drop materialized view mv_direct_path ;

Materialized view dropped.

SQL>
SQL> create table t_direct_path ( nu number(5) primary key);

Table created.

SQL>
SQL> create materialized view log on t_direct_path ;

Materialized view log created.

SQL>
SQL> create materialized view mv_direct_path
  2  refresh fast on demand as
  3  select * from t_direct_path;

Materialized view created.

SQL>
SQL> insert into t_direct_path values( 10000);

1 row created.

SQL> insert into t_direct_path values( 20000);

1 row created.

SQL> insert into t_direct_path values( 30000);

1 row created.

SQL> insert into t_direct_path values( 40000);

1 row created.

SQL> insert into t_direct_path values( 50000);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from t_direct_path;

        NU
----------
     10000
     20000
     30000
     40000
     50000

5 rows selected.

SQL> select * from mlog$_t_direct_path;

        NU SNAPTIME$ D O CHANGE_VECTOR$$                     XID$$
---------- --------- - - ------------------------------ ----------
     10000 01-JAN-00 I N FE                             1.6890E+15
     20000 01-JAN-00 I N FE                             1.6890E+15
     30000 01-JAN-00 I N FE                             1.6890E+15
     40000 01-JAN-00 I N FE                             1.6890E+15
     50000 01-JAN-00 I N FE                             1.6890E+15

5 rows selected.

SQL>
SQL> --those records are not visible in the MV log!
SQL> insert /*+ append*/ into t_direct_path
  2  (select level from dual connect by level < 20);

19 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from t_direct_path;

        NU
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
     10000
     20000
     30000
     40000
     50000

24 rows selected.

SQL> --still 5 rows
SQL> select * from mlog$_t_direct_path;

        NU SNAPTIME$ D O CHANGE_VECTOR$$                     XID$$
---------- --------- - - ------------------------------ ----------
     10000 01-JAN-00 I N FE                             1.6890E+15
     20000 01-JAN-00 I N FE                             1.6890E+15
     30000 01-JAN-00 I N FE                             1.6890E+15
     40000 01-JAN-00 I N FE                             1.6890E+15
     50000 01-JAN-00 I N FE                             1.6890E+15

5 rows selected.

SQL>
SQL> select * from ALL_SUMDELTA;

 TABLEOBJ# PARTITIONOBJ# D        SCN TIMESTAMP LOWROWID           HIGHROWID            SEQUENCE        XID
---------- ------------- - ---------- --------- ------------------ ------------------ ---------- ----------
    122504        122504 I 1.8447E+19 01-JAN-00 AAAd6IAAHAAAmiAAAA AAAd6IAAHAAAmiAH//          2 1.9704E+15

1 row selected.

SQL>
SQL> select object_name from user_objects
  2  where object_id in ( select TABLEOBJ# from ALL_SUMDELTA );

OBJECT_NAME
----------------------------------------
T_DIRECT_PATH

1 row selected.

SQL>
SQL> execute dbms_mview.refresh('MV_DIRECT_PATH', 'F');

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT * FROM mv_direct_path;

        NU
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
     10000
     20000
     30000
     40000
     50000

24 rows selected.

SQL>
SQL>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Jonas, June 20, 2017 - 5:38 am UTC

Thanks a lot! This is very helpful and the answer was so fast!! You are doing a great job!!

June 20, 2017 - 5:42 am UTC

glad we could help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Fast Refreshable Materialized Views - Direct Path Logs on the Master Table

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs