Nologging, index and direct-load insert

[ales]

Hi all,

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
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 status applies to tables only despite of 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 omitted)



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.



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

[pando]

redo are always generated, nologging minimize the amount but not to zero

opsc@MOVE>alter table obj nologging;

Table altered.

opsc@MOVE>set autotrace on

opsc@MOVE>insert /*+ append */ into obj select * from dba_objects where rownum < 100

99 rows created.

Statistics
----------------------------------------------------------
        221  recursive calls
         12  db block gets
         98  consistent gets
         10  physical reads
        296  redo size
        633  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
         99  rows processed

opsc@MOVE>alter table obj logging;

Table altered.

opsc@MOVE>insert into obj select * from dba_objects where rownum < 100;

99 rows created.

Statistics
----------------------------------------------------------
        151  recursive calls
         26  db block gets
         78  consistent gets
          0  physical reads
      10456  redo size
        647  bytes sent via SQL*Net to client
        489  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
         99  rows processed

296 redo size with nologging vs 10456 redo size with logging

[ales]

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.

[pando]

you mean that if you create an index with nologging then susquent insert shouldnt generate redo with that index...?

AFIAK if you reverse your process, first insert then create the index with nologging then you get minimum redo but if you insert with an index then it´s pretty normal?

[ales]

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.

[pando]

you mean that if you have an index with nologging attribute then the inserts afterwards should not generate redo?

direct load works if you use insert append or create index (and a few more) that doesnt mean that after you create an index with nologging then the redo would be small later



If I create an index with nologging clause on populated table, then redo is small.

this is normal because the index creation process is generating minimum redo

If I create an index with nologging clause on empty table and then populate the table with direct-load insert, then redo is big

this is normal too because the redo generated is for both table and index

[ales]

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.

[pando]

you cannot set an index to nologging, I see it as a normal behaviour, a table with an index obviously generates more redo than one table without index even the table is set to nologging

you can create an index with no logging but not alter it´s attribute

[ales]

Originally posted by pando
you cannot set an index to nologging

Yes, I see now and that's I'm worrying about :-), but doesn't manual say the exact opposite? (see my original post)

ALTER INDEX NOLOGGING really sets the NOLOGGING column in TABS to "NO".
But subsequent direct-load INSERT does not care and manual says it does.

Please kick me if I misunderstand it.

[pando]

well I make a pardon you can set to nologging an index

so basically you are saying that an table with nologging and its indexes with nologging is generating more redo than a table without indexes with nologging ?