Investigating Redo - Oracle Database Transactions and Locking Revealed - page 129

When the index is in LOGGING mode (the default), a rebuild of it generated about 600KB of redo. However, we can
alter the index:

EODA@ORA12CR1> alter index t_idx nologging;
Index altered.

EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.

EODA@ORA12CR1> alter index t_idx rebuild;
Index altered.

EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo)
|| ' bytes of redo generated...');
39352 bytes of redo generated...

PL/SQL procedure successfully completed.

And now it generates a mere 39KB of redo. But that index is “unprotected” now. If the data files it was located in
failed and had to be restored from a backup, we would lose that index data. Understanding that fact is crucial. The
index is not recoverable right now—we need a backup to take place. Alternatively, the DBA could just re-create the
index as we can re-create the index directly from the table data as well.

NOLOGGING Wrap-up
The operations that may be performed in a NOLOGGING mode are as follows:

ALTER s (rebuilds).

• Index creations and

INSERT s into a table using a direct-path INSERT such as that available via the /*+ APPEND */
hint or SQL*Loader direct-path loads. The table data will not generate redo, but all index
modifications will (the indexes on this nonlogged table will generate redo).

• Bulk

LOB operations (updates to large objects do not have to be logged). •

CREATE TABLE AS SELECT . • Table creations via

ALTER TABLE operations such as MOVE and SPLIT .
Used appropriately on an ARCHIVELOG -mode database, NOLOGGING can speed up many operations by dramatically
reducing the amount of redo log generated. Suppose you have a table you need to move from one tablespace to
another. You can schedule this operation to take place immediately before a backup occurs—you would ALTER the
table to be NOLOGGING , move it, rebuild the indexes (without logging as well), and then ALTER the table back to logging
mode. Now, an operation that might have taken X hours can happen in X/2 hours perhaps (I'm not promising a
50-percent reduction in runtime!). The appropriate use of this feature includes involving the DBA, or whoever is
responsible for database backup and recovery or any standby databases. If that person is not aware that you're
using this feature and a media failure occurs, you may lose data, or the integrity of the standby database might be
compromised. This is something to seriously consider.

• Various