NOLOGGING

NOLOGGING can be used to prevent bulk operations from logging too much information to Oracle's Redo log files. The nologging option is a great way to speed-up inserts and index creation.  It bypasses the writing of the redo log.

When you run nologging operations and you must:

Backup before and after - You must take a backup, both before and after all nologging operations.

With respect to the nologging option, three benefits listed in the Administrator's Guide are:

• Space is saved in the redo log files
• The time it takes to create the table is decreased
• Performance improves for parallel creation of large tables

Database noarchivelog mode - If your database is in "noarchivelog" mode and you are not using the APPEND hint for inserts, you WILL STILL generate redo logs!

Database archivelog mode - If you are in archivelog mode, the table must be altered to nologging mode and the SQL must be using the APPEND hint.  Else, redo WILL be generated.

You can use nologging for batch  inserts into tables and for creating indexes:

You can insert into tables with nologging - If you use the APPEND hint and place the table in nologging mode, redo will be bypassed.

alter table customer nologging;

insert /*+ append */ into customer values ('hello',';there');

You can create indexes with nologging - The only danger with using nologging is that you must re-run the create index syntax if you perform a roll-forward database recovery.  Using nologging with create index can speed index creation by up to 30%.

create index newidx . . . nologging;

Following operations can make use of the NOLOGGING option:

    SQL*Loader in direct mode
    INSERT /*+APPEND*/ ...
    CTAS
    ALTER TABLE statements (move/add/split/merge partitions)
    CREATE INDEX
    ALTER INDEX statements (move/add/split/merge partitions)





    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 (using APPEND)


The CREATE TABLE AS SELECT (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 operation are NOT sent to the redo logs.

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

APPEND:
#################

insert /*+ append */
into
   raleigh_customers
   raleigh_customers
     (select * from customers where name = 'RALEIGH');

By using the append hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table.

-- bulk inserts using the APPEND_VALUES hint.
l_start := DBMS_UTILITY.get_time;

FORALL i IN l_tab.first .. l_tab.last
INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i)

The APPEND hint tells the optimizer to perform a direct-path insert, which improves the performance of INSERT .. SELECT operations for a number of reasons:

Data is appended to the end of the table, rather than attempting to use existing free space within the table.
Data is written directly to the data files, by-passing the buffer cache.

How the APPEND Hint Affects the Table Size (High Water Mark)
------------------------------------------------------------------------------------------------------
As direct-path inserts append data to the end of the table, they constantly increase the table high water mark, even if there is lots of free space within the table. In tables that regularly have rows deleted, the use of the the APPEND hint can result in large tables containing lots of sparsely populated blocks. These will need to be managed by one of the following types of shrink operation.

Export the data, truncate the table and import the data.
Use a "CREATE TABLE ... AS SELECT" (CTAS) operation to build a new table with the data compacted, drop the original table and rename the new table to replace the original.
Use and online table redefinition operation to recreate the table.
Use an online segment shrink operation to compact the data

In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING

ALTER TABLE t1 NOLOGGING;