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.



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