Yury recently raised a topic about nologging segments in e-Business Suite database. The discussion later evolved to on how indexes are affected by the nologging setting (is it worth putting indexes in nologging mode to reduce the redo generation). I wasn’t quite sure about the answer, so I decided to test it myself. Read on for results of 22 short testcases (I think the results turned out to be quite interesting and worth knowing).

Shortly about the test, it’s 11.2.0.1 database, 2 tablespaces (nologging and force logging) - created similarilly to those in eBS OATM setup. Redo size for following operations will be tested: insert, insert with append hint, update for one of the columns, index rebuild online. Each of operations will be performed on table with indexes (in different logging/nologging combinations). Let’s start with the results:

The test results

The test results

I marked red the results where one could have expected a decrease of redo size, and green where the expectations fulfilled.

The thing with the red ones is that the index maintenance during DML operations is always a LOGGING operation, we can see a partial redo size reduction for insert with append hint operations which is explained by the fact that index maintenance is deferred, so in these cases it’s carried out only when the table has been loaded.

If we check the “insert_append” column some relations can be drawn, which means the nologging setting is nicely applied to the table each time the insert with append is done

  • “log_table + no_index” - “nolog_table + no_index” = ~43Mb
  • “log_table + log_index” - “nolog_table + log_index” = ~43Mb
  • “log_table + nolog_index” - “nolog_table + nolog_index” = ~43Mb

The most surprising result here in my opinion is that the index maintenance still generates redo even if redo is not generated for the table. What’s the point of it? It does not help me in case of recovery at all. (The redo generation for the nologging indexes can be avided by setting the index in UNUSABLE state before the DML operation, and by a rebuild followed after the DML - but this has to be specifically coded)

The only way how nologging affects the redo generation for index operations is during rebuild (online or not) and also create index statements (not tested here), those are marked green in the table above along with the loading data into the table without any indexes.

Click on the “show source” below if you want to see what I was actually doing to perform the test, including the tablespace creation, way of checking the redo size, etc.:


SQL> SELECT LOG_MODE, FORCE_LOGGING FROM V$DATABASE;

LOG_MODE     FOR
------------ ---
ARCHIVELOG   NO

Elapsed: 00:00:00.02

SQL> SET HEAD OFF VERIFY OFF FEED OFF TIMING OFF

SQL> alter session set optimizer_dynamic_sampling=0;

SQL> DROP TABLESPACE T_LOG_TBS INCLUDING CONTENTS AND DATAFILES;
SQL> DROP TABLESPACE T_NOLOG_TBS INCLUDING CONTENTS AND DATAFILES;

SQL> CREATE TABLESPACE T_LOG_TBS DATAFILE SIZE 500M
  2  AUTOEXTEND OFF FORCE LOGGING
  3  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;

SQL> CREATE TABLESPACE T_NOLOG_TBS
  2  DATAFILE SIZE 500M
  3  AUTOEXTEND OFF NOLOGGING
  4  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K SEGMENT SPACE MANAGEMENT AUTO;

SQL> DROP TABLE TEST_SOURCE;

SQL> CREATE TABLE TEST_SOURCE TABLESPACE USERS AS
  2  SELECT * FROM DBA_OBJECTS,
  3    (SELECT rownum rn FROM dual CONNECT BY level<=5
  4    );

SQL> SELECT bytes FROM user_segments WHERE segment_name='TEST_SOURCE';

  45088768

SQL> col R1 NOPRINT NEW_VALUE R1
SQL> col R2 NOPRINT NEW_VALUE R2


SQL> DROP table TEST_DEST;
DROP table TEST_DEST
           *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> CREATE table TEST_DEST tablespace T_LOG_TBS as select * from TEST_SOURCE where 1=0;
SQL> insert into TEST_DEST select * from TEST_SOURCE;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table no_index: 44585428

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table no_index 45005652

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table no_index: 183455584

SQL> create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table log_index 245471824

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table log_index 159382864

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table log_index: 420821388

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_LOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE log_table rebuild_online log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE log_table rebuild_online log_index: 32297556

SQL> drop index TEST_LOG_IDX;
SQL> create index TEST_NOLOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend log_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend log_table nolog_index 245564848

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append log_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append log_table nolog_index 159186324

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update log_table nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update log_table nolog_index: 420795224

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_NOLOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE log_table rebuild_online nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE log_table rebuild_online nolog_index: 846412

SQL> DROP table TEST_DEST;
SQL> CREATE table TEST_DEST tablespace T_NOLOG_TBS as select * from TEST_SOURCE where 1=0;
SQL> insert into TEST_DEST select * from TEST_SOURCE;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table no_index 44606672

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table no_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table no_index 767684

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table no_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table no_index: 188704492

SQL> create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table log_index 245396952

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table log_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table log_index 115176588

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table log_index: 420744288

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_LOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE nolog_table rebuild_online log_index: '||(&R2-&R1) T1 from dual;

REDO SIZE nolog_table rebuild_online log_index: 32312396

SQL> drop index TEST_LOG_IDX;
SQL> create index TEST_NOLOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_noappend nolog_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_noappend nolog_table nolog_index 245784828

SQL> truncate table TEST_DEST;
SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE insert_append nolog_table nolog_index '||(&R2-&R1) T1 from dual;

REDO SIZE insert_append nolog_table nolog_index 115763064


SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
SQL> commit;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE update nolog_table nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE update nolog_table nolog_index: 420846428

SQL> select value R1 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> alter index TEST_NOLOG_IDX rebuild online;
SQL> select value R2 from v$mystat s,v$statname n where s.statistic#=n.statistic# and n.name='redo size';

SQL> select 'REDO SIZE nolog_table rebuild_online nolog_index: '||(&R2-&R1) T1 from dual;

REDO SIZE nolog_table rebuild_online nolog_index: 846532

Here are the explanations of the abbreviatures used above:

  • insert_noappend: simple insert-as-select statement into an empty table:
insert into TEST_DEST select * from TEST_SOURCE;
  • insert_append: insert-as-select statement with append hint into an empty table:
insert /*+ append */into TEST_DEST select * from TEST_SOURCE;
  • update: update of the 2nd column in the index, update statement is written so that it would update the same rows the same way in each test case:
update TEST_DEST set object_name=substr(to_char(length(owner||object_name))||object_name,1,30);
  • rebuild_online: rebuild online operation of the index:
alter index TEST_LOG_IDX rebuild online;
alter index TEST_NOLOG_IDX rebuild online;
  • log_table: table used for tests created in the FORCE_LOGGING enabled tablespace:
CREATE table TEST_DEST tablespace T_LOG_TBS as select * from TEST_SOURCE where 1=0;
  • nolog_table: table used for tests created in the NOLOGGING enabled tablespace:
CREATE table TEST_DEST tablespace T_NOLOG_TBS as select * from TEST_SOURCE where 1=0;
  • no_index: there is no index on the table TEST_DEST
  • log_index: index on TEST_DEST is created in the FORCE_LOGGING enabled tablespace:
create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_LOG_TBS;
  • log_index: index on TEST_DEST is created in the NOLOGGING enabled tablespace:
create index TEST_LOG_IDX on TEST_DEST (owner, object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME) tablespace T_NOLOG_TBS;