NOLOGGING and index

From: Ales Voboril <alesv_at_post.cz>
Date: Fri, 12 Oct 2001 11:25:07 +0200
Message-ID: <9q6crl$232v$1@ns.felk.cvut.cz>

Hi all,

I have question about NOLOGGING state of index and direct-load insert.

From Oracle doco8.1.7:

TUNING PARALLEL EXECUTION:
"When a table or index has NOLOGGING set, neither parallel nor serial direct-load INSERT operations generate undo or redo logs."

ALTER INDEX NOLOGGING :
"Use LOGGING or NOLOGGING to specify whether subsequent Direct Loader (SQL*Loader) and direct-load INSERT operations against a nonpartitioned index, a range or hash index partition, or all partitions or subpartitions of a composite-partitioned index will be logged (LOGGING) or not logged (NOLOGGING) in the redo log file."

BUT ...
when I create a table and set it as nologging, subsequent direct-load INSERT makes reasonable amount of redo.
After truncating the table I create an ordinary index on that table, set it nologging as well and make direct-load INSERT again. The redo is huge. It seems that NOLOGGING state applies to tables only despite manual. Is that bug or feature?

My Oracle is 8.1.7 EE on AIX

Thanks in advance,
Ales

Testing report follows:

SQL>create table t nologging as select * from all_objects where 1=0;

Table created.

SQL>set autotrace on statistics
SQL>insert /*+ APPEND */ into t select * from all_objects;

21734 rows created.

Statistics

        786  recursive calls
        154  db block gets
      83190  consistent gets
          0  physical reads
      19616  redo size
        493  bytes sent via SQL*Net to client
        466  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
      21734  rows processed

SQL>commit;

Commit complete.

SQL>truncate table t;

Table truncated.

SQL>create index t_ind on t (object_id) nologging;

Index created.

SQL>select table_name, logging from tabs where table_name='T' 2 union
3 select index_name, logging from ind where table_name='T';

TABLE_NAME                     LOG
------------------------------ ---
T                              NO
T_IND                          NO

Statistics

         14  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        629  bytes sent via SQL*Net to client
        435  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL>insert /*+ APPEND */ into t select * from all_objects;

21735 rows created.

Statistics

       1972  recursive calls
       1437  db block gets
      83613  consistent gets
        319  physical reads
    1484268  redo size
        494  bytes sent via SQL*Net to client
        466  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          1  sorts (disk)
      21735  rows processed

SQL>commit;

Commit complete.

Received on Fri Oct 12 2001 - 04:25:07 CDT