nologging clause

[manjunathk]

Hi friends,
We are using the nologging clause for the tables which are partitioned. Lot of insert activity is going on in these tables.
I understand, that "nologging" will result in disabling the redo logs for the initial insert. What is the implication? Suppose, I am populating the table every 2 hours, and the operation takes around 1 hour. Does it mean that only the first inserts are not logged but all the subsequent inserts from hour 2, 4 6 etc are logged?
Also (though I suspect the answer), how can I ensure that this is actually the case?
Thanks
manjunath

[inosov]

Be aware that only the following operations can make use of the NOLOGGING option:

alter table...move partition
alter table...split partition
alter index...split partition
alter index...rebuild
alter index...rebuild partition
create table...as select
create index
direct load with SQL*Loader
direct-load INSERT

If the NOLOGGING option is defined on the table level, then ALL direct-load INSERTS
will use this option, not only the first insert.

(Don't forget /*+ APPEND */ hint if your insert works in serial mode,
for parallel mode APPEND is by default).

[jmodic]

Originally posted by manjunathk
Suppose, I am populating the table every 2 hours, and the operation takes around 1 hour. Does it mean that only the first inserts are not logged but all the subsequent inserts from hour 2, 4 6 etc are logged?

No, it is not even so. Very probably *all* of your insert batches are fully logged. Inserts are not logged only in some very specific cases, basicaly when you use SQL*LOader's direct path option and when you implement "direct-load inserts". I suggest you take a look at Oracle Concepts manual, where (almost) the whole chapter is devoted to direct-load inserts.

If you wan't to make sure that your inserts are realy happening in nologging mode, you can make the following simple test, probably on a test database, running in archivelog mode:
1.Make a database backup
2.Perform inserts
3.Restore the database from a backup and perform full media recovery
4.If after the recovery the inserted rows are there, then inserts were logged. If the rows are not ther, your inserts were actually direct-load inserts and were not logged.

[manjunathk]

Thanks, jmodic and inosov.
We are running in 'Noarchive' mode. We have the option of reloading the entire insert and so have done away with logging.
Still, I have noticed that the logswitches are occuring frequently. Presently queries are not going on. So, I thought if there is a way to know what is being written into redo log files, it would solve my problem. Does logminer help in this situation(i have never used it so far). I am running 8.1.6 on Tru64.
Thanks
manjunath

[jmodic]

Yes, logminer will show you if the inserts are logged.

[chrisrlong]

Originally posted by inosov
(Don't forget /*+ APPEND */ hint if your insert works in serial mode,
for parallel mode APPEND is by default).
[/B]

Parallel queries do not require the APPEND hint, as inosov said. However, the session apparently needs to have ALTER SESSION ENABLE/FORCE PARALLEL DML in order to 'turn on' the direct-insert mode for parallel queries.

- Chris