Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Option NOLOGGING for indexes

Re: Option NOLOGGING for indexes

From: Jusung Yang <JusungYang_at_yahoo.com>
Date: 12 Mar 2003 22:30:59 -0800
Message-ID: <130ba93a.0303122230.73360856@posting.google.com>


"Nologging" clause can be confusing sometime. For indexes, I believe it applies only to index creation and rebuilt operations. After that, indexes will be fully logged - even though user_indexes.logging says "NO". In the following test, you can see the "redo size" during insert jumped from 1meg to 10meg after index is created (even though it was created with nologging option). It went down to 1meg after index is dropped.

SQL> set autotrace on statistics
SQL> insert into t1 select object_id, object_name from all_objects;

30234 rows created.

Statistics


        465  recursive calls
       2054  db block gets
     156271  consistent gets
         54  physical reads
    1247636  redo size
        620  bytes sent via SQL*Net to client
        557  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      30234  rows processed

SQL> rollback;

Rollback complete.

SQL> create index t1_idx on t1(object_id, object_name) nologging;

Index created.

SQL> insert into t1 select object_id, object_name from all_objects;

30235 rows created.

Statistics


        387  recursive calls
      39380  db block gets
     157138  consistent gets
          2  physical reads
   10417964  redo size
        621  bytes sent via SQL*Net to client
        557  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      30235  rows processed

SQL> rollback;

Rollback complete.

SQL> drop index t1_idx;

Index dropped.

SQL> insert into t1 select object_id, object_name from all_objects;

30234 rows created.

Statistics


        178  recursive calls
       1225  db block gets
     156156  consistent gets
          2  physical reads
    1197700  redo size
        622  bytes sent via SQL*Net to client
        557  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
      30234  rows processed

SQL> Rick Denoire <100.17706_at_germanynet.de> wrote in message news:<nlav6v0dqt51de48516k3ethhvhgckb4fq_at_4ax.com>...
> I would agree that in general, changes of valuable data in tables
> should be done with the logging option. But what could be the
> consequence of using the NOLOGGING option for indexes? One could set
> this option at the tablespace level. It would be tolerable for me if
> the index resulted being unusuable after an instance error, since it
> is always possible to rebuild it. Or am I missing something?
>
> Thanks
> Rick Denoire
Received on Thu Mar 13 2003 - 00:30:59 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US