I was trying to understand how redo works in nologging mode. Here is a interesting situation i encountered in 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
Here is the script i used to generate the above. As you can see, i was not expecting the redo log as i acreate a nologging table and append hint. But, i see the redo blocks. At the same time when i create table in normal mode and do not use append hint, there is zero redo generated. What am I missing?
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;
I've made a slight adjustment to your script to demonstrate the true difference
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>
So insert-append = 34k redo, normal insert = 127meg of redo !
A direct mode operation in nologging mode means we do not have log redo for the *data* that you are inserting. However, we still need to log redo for anything that happens in the dictionary because of this insert. For example, we might allocate some extents, we might need to adjust your remaining quota for the tablespace etc etc etc. These *dictionary* operations must be logged.
In your "no redo was used" example, because we created so little data, we didn't need to do dictionary work.