APPEND, NOLOGGING and Indexes

Running some tests on 10gR2, I find that the presence of Indexes can have a very significant impact on Direct Path Inserts done with the APPEND Hint, even if the target table is NOLOGGING.

Thus, for example, a Normal INSERT on an unindexed table is :

SQL> insert /* normal insert */ into test_append ta
  2  select * from source_table;

301974 rows created.

Elapsed: 00:00:03.22
SQL> commit;

Commit complete.

Elapsed: 00:00:00.01
SQL> select sn.name, s.value
  2  from v$statname sn, v$mystat s
  3  where sn.statistic#=s.statistic#
  4  and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
  5  order by sn.name;

NAME                                                                       VALUE
---------------------------------------------------------------- ---------------
CPU used by this session                                                     145
consistent gets                                                           12,608
db block changes                                                          30,725
db block gets                                                             36,672
physical reads direct                                                          0
physical writes direct                                                         0
redo entries                                                              22,796
redo size                                                             33,808,976
undo change vector size                                                1,122,508

While an INSERT with the table set to NOLOGGING is :

SQL> insert /*+ APPEND */ into test_append ta
  2  select * from source_table;

301980 rows created.

Elapsed: 00:00:01.61
SQL> commit;

Commit complete.

Elapsed: 00:00:00.03
SQL> select sn.name, s.value
  2  from v$statname sn, v$mystat s
  3  where sn.statistic#=s.statistic#
  4  and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
  5  order by sn.name;

NAME                                                                       VALUE
---------------------------------------------------------------- ---------------
CPU used by this session                                                     129
consistent gets                                                            4,353
db block changes                                                             194
db block gets                                                              4,353
physical reads direct                                                          0
physical writes direct                                                     4,146
redo entries                                                                 260
redo size                                                                 26,816
undo change vector size                                                    4,664

However, if I have a "large" (multi column, concatenated index with a large key size) Index on the target table I get :

SQL> insert /*+ APPEND */ into test_append ta
  2  select * from source_table;

301974 rows created.

Elapsed: 00:00:24.45
SQL> commit;

Commit complete.

Elapsed: 00:00:00.18
SQL> select sn.name, s.value
  2  from v$statname sn, v$mystat s
  3  where sn.statistic#=s.statistic#
  4  and sn.name in ('CPU used by this session', 'consistent gets', 'db block changes', 'db block gets', 'physical reads direct','physical writes direct','redo entries', 'redo size','undo change vector size')
  5  order by sn.name;

NAME                                                                       VALUE
---------------------------------------------------------------- ---------------
CPU used by this session                                                   1,107
consistent gets                                                           21,592
db block changes                                                         186,229
db block gets                                                            194,635
physical reads direct                                                      4,775
physical writes direct                                                     8,921
redo entries                                                             100,228
redo size                                                            152,819,412
undo change vector size                                               72,298,884

When a Direct Path INSERT is executed Index Maintenance is deferred. However, Index Maintenance itself generates Undo and is NOT a NoLogging operation.

Thus, my INSERT that normally generates 33MB of Redo and can be optimized to 26KB with NOLOGGING set at the Table level and APPEND as a Hint (directive), becomes a 152MB Redo Generator if I have an Index.

YMMV, depending on the table columns, Index key length etc. But it is important to note that an Index can really hurt your happiness when you are doing bulk loads.