Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

NOLOGGING and index

3 views
Skip to first unread message

Ales Voboril

unread,
Oct 12, 2001, 5:25:07 AM10/12/01
to
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.

***********************************************************************

Ales Voboril

unread,
Oct 17, 2001, 6:53:10 AM10/17/01
to
Hi again,
I see I was a bit verbose last week, so I try to summarize it:

I found that in case of direct-load INSERT, indexes on the populated
NOLOGGING table generate significant amount of redo regardless of their
NOLOGGING status.

Could anybody confirm or disconfirm that?

Thanks in advance,
Ales


Ronald

unread,
Oct 17, 2001, 10:29:49 AM10/17/01
to
"Ales Voboril" <al...@post.cz> wrote in message news:<9qjnt0$1po0$1...@ns.felk.cvut.cz>...

You are right. For some reason the nologging does not seem to work on
the index. Maybe it is not so strange; sqlload did put them in
directload status. Just drop and recreate the indexes when ready.

Ronald.
-----------------------
http://ronr.nl/unix-dba

Thomas Kyte

unread,
Oct 17, 2001, 1:40:00 PM10/17/01
to
In article <9qjnt0$1po0$1...@ns.felk.cvut.cz>, "Ales says...

Yes, it makes sense when you think about it...

The TABLE data is just written above the HWM. If we need to "rollback", we just
never advance the HWM.

For the indexes however, a complex data structure, our new data is intermingled
with the old data. In the event of a rollback -- we have to be able to UNDO all
of the work we just did to that index.

the direct path insert can be used to load a table without much redo genearation
but the indexes must be fully protected as we load.

--
Thomas Kyte (tk...@us.oracle.com) http://asktom.oracle.com/
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/
Opinions are mine and do not necessarily reflect those of Oracle Corp

Ales Voboril

unread,
Oct 18, 2001, 2:45:08 AM10/18/01
to
>Just drop and recreate the indexes when ready.

Thank you, Ronald.
I'll do that this way.
Ales

Ales Voboril

unread,
Oct 18, 2001, 3:06:46 AM10/18/01
to
Thanks for your reply, Tom.

>
> Yes, it makes sense when you think about it...
>
> The TABLE data is just written above the HWM. If we need to "rollback",
we just
> never advance the HWM.
>
> For the indexes however, a complex data structure, our new data is
intermingled
> with the old data. In the event of a rollback -- we have to be able to
UNDO all
> of the work we just did to that index.

Yes, it makes sense.

Then, the Oracle documentation is confusing at that point, isn't it?

Ales


0 new messages