Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Option NOLOGGING for indexes
"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