Oracle Database Tips by Donald BurlesonApril 28, 2015. Nologging Table Still generating Lots Of Archive Logs [ID 976722.1] SQL*Loader - Using the NOLOGGING and UNRECOVERABLE options [ID 160092.1] The Gains and Pains of Nologging Operations in a Data …

Then instead of deleting the data it's a truncate/drop partition operation. The nologging option is a great way to speed-up inserts and index creation. Otherwise it will take tablespace option as default. the DELETE must be deleting from multiple partitions instead just a single partition. LOGGING/NOLOGGING helps manage enabling direct path writes in order to reduce the generation of REDO and UNDO. Direct-load INSERT enhances performance during insert operations by formatting and writing data directly into Oracle datafiles, without using the buffer cache. The table high water mark is unrelated to indexes. Ask TOM . - Do the delete as a "create table as select" - Partition the table.

It bypasses the writing of the redo log, significantly improving performance. Answered by: Tom Kyte - Last updated: … I'm doubtfull about the CTAS option as monthly i delete about 30m rows & that table has around 500m rows.so with CTAS i'll have to create a table with 470m data. Site Feedback; Sign In; Questions; Office Hours; Resources ; About; Questions; Deleting many rows from a big table; Breadcrumb. Oracle nologging tips.

This will be far less redo than the delete. INSERT/UPDATE/DELETE will generate redo log on NOLOGGING mode ? - Do the delete as a "create table as select" - Partition the table.

Skip to Main Content.

1. Investigate these!

However please remember, most DML operation (insert, update, delete) are logged no matter what you have on logging/nologging The CTAS with NOLOGGING or UNRECOVERABLE will send the actual create statement to the redo logs (this information is needed in the data dictionary), but all rows loaded into the table during the … DELETE parallel will only work if the table is partitioned and if there are multiple partitions involved in the operation i.e. This functionality is similar to that of the Direct Loader utility (SQL*Loader).

1. This will be far less redo than the delete. 2. Hi, Be very careful using UNRECOVERABLE clause (Oracle7) and the NOLOGGING clause (Oracle8) when performing CREATE INDEX or CREATE TABLE AS SELECT (CTAS) commands. Question and Answer. Then instead of deleting the data it's a truncate/drop partition operation. Asked: January 02, 2002 - 10:07 am UTC.

So if you explicitly specify logging/nologging, then table attribute wins. Nologging does not work (yes it does...) Breadcrumb.

You'll need to move the table to reclaim this space. Oracle Architecture Background Information. You'll need to move the table to reclaim this space. The one on Data Guard has the command to allow and to disallow nologging at the database level examples. It is one of several ways to control the delicate balance between recoverability and performance. After I read about Redo & Undo in Expert Oracle Database Architecture book by Kyte, He told about NOLOGGING... Nologging doesn't mean all operations in that object will not generate redo log.

The table high water mark is unrelated to indexes.

2. REDO is how Oracle …

Thanks for the question, Pablo. Investigate these! Here are some Oracle support documents on the subject. Introduction to Direct-Load INSERT .

Developers and DBAs get help from Oracle experts on: Deleting many rows from a big table. Question and Answer.