Oracle 批量负载上的实体化视图日志性能

关于物化视图日志。他们如何处理批量插入？

我在某个地方看到它提到使用序列在物化视图日志上建立一个范围。

例如:
我们有3个底层表

表A
表B
表C

这些表组合在一起形成一个物化视图 'ABC'。那个物化的观点 “abc” 是按需的，完全刷新了。由夜间批处理过程刷新，该过程将信息传播到表A，B和C中。

In order to help improve the nightly refresh process of the materialized view I would like to incorporate materialized view logs on the underlying table and implement a fast refresh instead of a complete refresh. (No need to replicate the same data over and over again since the underlying tables keep growing in size). Lets say we have MVLOG_A now collecting change information on 表A.

My nightly load process of 表A now inserts 10,000 records. If I create my materialized view log on row-id will it simply take that range or process each individual insertion as a records in the MVLOG_A. Which would imply that the underlying trigger is firing multiple times?

感谢您的输入。我只想确保在实现任何操作之前，我对此有一个全面的了解。
戴夫

是的，MV日志将为您插入的每一行记录一个条目。它不能依赖rowid-ranges或其他技巧，因为其他会话可能同时插入行!

具体捕获多少数据取决于您如何创建MV日志。基本日志捕获最少的信息，但您也可以告诉它记录新值列和其他元数据:

create table t (
  c1 int primary key,
  c2 int,
  c3 date,
  c4 varchar2(100)
);

create materialized view log on t;

desc mlog$_t

Name               Null?    Type            
C1                          NUMBER           
SNAPTIME$$                  DATE             
DMLTYPE$$                   VARCHAR2(1)      
OLD_NEW$$                   VARCHAR2(1)      
CHANGE_VECTOR$$             RAW(255 BYTE)    
XID$$                       NUMBER  

drop materialized view log on t;
create materialized view log on t
  with primary key, rowid, sequence ( c2, c3, c4 )
  including new values;
  
desc mlog$_t

Name               Null?    Type            
C1                          NUMBER           
C2                          NUMBER           
C3                          DATE             
C4                          VARCHAR2(100)    
M_ROW$$                     VARCHAR2(255)    
SEQUENCE$$                  NUMBER           
SNAPTIME$$                  DATE             
DMLTYPE$$                   VARCHAR2(1)      
OLD_NEW$$                   VARCHAR2(1)      
CHANGE_VECTOR$$             RAW(255 BYTE)    
XID$$                       NUMBER

捕获这些额外的细节可以使刷新更快，因为它具有所需的所有信息。

但是它对插入有什么影响？

让我们测试一下:

declare
  start_time pls_integer;
  procedure ins_rows ( num_rows integer ) as
  begin
  insert into t 
    with rws as (
      select level x from dual
      connect by level <= num_rows
    )
      select x, mod ( x, 37 ), sysdate + x, 
             lpad ( 'x', 100, 'x' )
      from   rws;
  end;
begin
  start_time := dbms_utility.get_time ();
  ins_rows ( 10000 );
  dbms_output.put_line ( 
    'Run time = ' || ( dbms_utility.get_time () - start_time ) 
  );
  execute immediate 'drop materialized view log on t';
  execute immediate 'create materialized view log on t';
  execute immediate 'truncate table t';
  start_time := dbms_utility.get_time ();
  ins_rows ( 10000 );
  dbms_output.put_line ( 
    'Run time = ' || ( dbms_utility.get_time () - start_time ) 
  );
  execute immediate 'drop materialized view log on t';
  execute immediate 'truncate table t';
  start_time := dbms_utility.get_time ();
  ins_rows ( 10000 );
  dbms_output.put_line ( 
    'Run time = ' || ( dbms_utility.get_time () - start_time ) 
  );
end;
/

Run time = 246
Run time = 235
Run time =  16

因此，是的，添加日志确实需要更长的时间 (〜2.5秒vs 10分之1秒)，但是日志类型之间的差异很小。瘦日志仅比捕获所有内容的日志快一点。

这是可以接受的开销吗？只有你能回答!

测试和基准测试，看看它对您的表有什么影响，以评估较慢的插入和较快的刷新到较快的插入和 (缓慢？) 完全刷新的权衡。请记住，快速刷新会更好地扩展; 随着时间的流逝，完整的刷新将逐渐变慢 (假设您保留了所有数据)。