I posted this question to usenet last week but no response so far :-(.
I'm sure somebody here tells me what's wrong.
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
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 status applies to tables only despite of manual.
Pando, thanks for reply.
Yes, I understand there must be some redo.
Just see again my original post.
Populating the table with over 20,000 rows makes about 20 K redo. That's great and works as I'd expect.
But the same count of rows inserted into the same table with NOLOGGING index makes about 1,5M redo. That is the problem. It seems that subsequent maintenance of nologgingg index after direct-load insert is fully loged in redo. But (if I understand it) it shouldn't.
Originally posted by pando you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?
No. Subsequent direct-load insert shouldn't.
If I create an index with nologging clause on populated table, then redo is small.
If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big.
Hi, I'm sorry for the delay.
I don't want to annoy with full report of testing,
so have a look on the following summary table:
Operation Bytes of redo
create table nologging ........................ 39172
direct-load insert of 22,000 rows ............. 19613
create index nologging on populated table ..... 33456
truncate table ................................ 22556
direct-load insert of 22,000 rows
with both table and index set to nologging .... 1487556
I hope it's clear what I mean: the redo generated with the last operation should be something about 55K, not 1.5 M.
That's why I mean something is wrong with NOLOGGING.