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.
***********************************************************************
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
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
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
Thank you, Ronald.
I'll do that this way.
Ales
>
> 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