Oracle 在nologging模式下重做日志

我试图了解重做在nologging模式下是如何工作的。这里是我在XE中遇到的一个有趣的情况。

SQL> @redo_question.sql

Table dropped.


Table created.


LOG_MODE
------------
NOARCHIVELOG


Statistics
----------------------------------------------------------
        437  recursive calls
          6  db block gets
        912  consistent gets
         18  physical reads
        340  redo size
        852  bytes sent via SQL*Net to client
       1052  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        112  sorts (memory)
          0  sorts (disk)
          9  rows processed


Statistics
----------------------------------------------------------
         44  recursive calls
         26  db block gets
         83  consistent gets
          0  physical reads
          0  redo size
        870  bytes sent via SQL*Net to client
       1039  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          9  rows processed

这里是我用来生成上面的脚本。如您所见，当我创建一个nologging表并附加提示时，我并不期望重做日志。但是，我看到重做块。同时，当我在正常模式下创建表并且不使用追加提示时，会生成零重做。我错过了什么？

set  autotrace off
set timing off
set linesize 2000
drop table t1;

create table t1 nologging as select object_name,object_id,owner from all_objects where object_id<10;
/*
alter table t1 add constraint t1_pk primary key(object_name);

create index t1_idx on t1(object_id);

begin
for i in (select constraint_name, table_name from user_constraints where table_name='T1') LOOP
execute immediate 'alter table '||i.table_name||' disable constraint '||i.constraint_name||'';
end loop;
end;
/
begin
for i in (select index_name from user_indexes where table_name='T1') LOOP
execute immediate 'alter index '||i.index_name||' unusable';
end loop;
end;
/
*/
select log_mode from v$database;
set define off
set feedback off
set autotrace traceonly statistics
insert/*+ append */ into t1 select object_name,object_id,owner from all_objects where object_id>10 and object_id<20;
commit;

set autotrace off

drop table t1;
create table t1 logging as select object_name,object_id,owner from all_objects where object_id<10;
alter table t1 add constraint t1_pk primary key(object_name);
create index t1_idx on t1(object_id);
set autotrace traceonly statistics
insert into t1 select object_name,object_id,owner from all_objects where object_id>10 and object_id<20;
commit;

我对你的脚本做了一点调整，以证明真正的不同

SQL> set  autotrace off
SQL> set timing off
SQL> set linesize 2000
SQL> drop table t1;

Table dropped.

SQL>
SQL> create table t1 nologging as select object_name,object_id,owner from all_objects where object_id<10;

Table created.

SQL>
SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

1 row selected.

SQL> set define off
SQL> set feedback off
SQL> set autotrace traceonly statistics
SQL> insert/*+ append */ into t1 select object_name,object_id,owner from all_objects;

Statistics
----------------------------------------------------------
        252  recursive calls
       1070  db block gets
      91776  consistent gets
          0  physical reads
      38788  redo size
        848  bytes sent via SQL*Net to client
       1000  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        840  sorts (memory)
          0  sorts (disk)
      80907  rows processed

SQL> commit;
SQL>
SQL> set autotrace off
SQL>
SQL> drop table t1;
SQL> create table t1 logging as select object_name,object_id,owner from all_objects where object_id<10;
SQL> create index t1_idx on t1(object_id);
SQL> set autotrace traceonly statistics
SQL> insert into t1 select object_name,object_id,owner from all_objects;

Statistics
----------------------------------------------------------
        179  recursive calls
      13298  db block gets
      94490  consistent gets
          1  physical reads
   12740264  redo size
        864  bytes sent via SQL*Net to client
        987  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
        840  sorts (memory)
          0  sorts (disk)
      80908  rows processed

SQL> commit;
SQL>
SQL>

所以插入-追加 = 34k重做，正常插入 = 127meg重做!

在nologging模式下的直接模式操作意味着我们没有对要插入的 * data * 进行日志重做。但是，由于此插入，我们仍然需要记录重做字典中发生的任何事情。例如，我们可能会分配一些范围，我们可能需要调整表空间的剩余配额等。必须记录这些 * 字典 * 操作。

在您的 “未使用重做” 示例中，因为我们创建的数据很少，所以我们不需要进行字典工作。