nologging clause
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: nologging clause

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    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

  2. #2
    Join Date
    Apr 2001
    Posts
    37
    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).
    inosov
    Brainbench MVP for Oracle DBA

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Oct 2000
    Posts
    211
    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

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, logminer will show you if the inserts are logged.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width