Oracle 快速可刷新的实体化视图-主表上的直接路径日志

你好，主人 :-)，

我阅读了有关快速可刷新的具体化视图的文档。它指出:

常规DML更改的更改存储在与主表关联的实体化视图日志中。直接路径插入操作的更改存储在直接加载器日志中。

我用下面的测试用例检查了这个行为:

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;
复制

我想知道直接路径负载的另外19行到底是如何成功地转移到MV的？是否有任何选项 (数据字典视图，日志文件或类似MLOG $ 对象)，我将能够看到有多少行已通过直接路径加载插入到主表中？

提前谢谢!你是一个了不起的团队!
乔纳斯

“ALL_SUMDELTA” 是此处缺少的一块拼图，从11.2.0.2开始可用。您的 (优秀) 示例转载如下

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>
复制