There are two ways to use the NOLOGGING option. You have already seen one method— embedding the NOLOGGING keyword in the SQL command. The other method, which involves setting the NOLOGGING attribute on the segment (index or table), allows certain operations to be performed implicitly in a NOLOGGING mode. For example, I can alter an index or a table to be NOLOGGING by default. This means for the index that subsequent rebuilds of this index will not be logged (the index will not generate redo; other indexes and the table itself might, but this index will not). Using the table T we just created, we can observe

$ sqlplus eoda/foo@PDB1
SQL> select log_mode, force_logging from v$database;
SQL> create index t_idx on t(object_name); Index created.
SQL> variable redo number
SQL> exec :redo := get_stat_val( ‘redo size’ );PL/SQL procedure successfully completed.
SQL> alter index t_idx rebuild;Index altered.
SQL> exec dbms_output.put_line( (get_stat_val(‘redo size’)-:redo)
|| ‘ bytes of redo generated…’);672264 bytes of redo generated…
PL/SQL procedure successfully completed.

Note Again, this example was performed in an ARCHIVELOG mode database. You would not see the differences in redo size in a NOARCHIVELOG mode database as the index CREATE and REBUILD operations are not logged in NOARCHIVELOG mode.

When the index is in LOGGING mode (the default), a rebuild of it generated about 600KB of redo. However, we can alter the index:
SQL> alter index t_idx nologging;Index altered.
SQL> exec :redo := get_stat_val( ‘redo size’ ); PL/SQL procedure successfully completed.
SQL> alter index t_idx rebuild;Index altered.
SQL> 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 datafiles 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:
•\ Index creations and ALTERs (rebuilds).
•\ Bulk INSERTs 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).
•\ LOB operations (updates to large objects do not have to be logged).
•\ Table creations via CREATE TABLE AS SELECT.
•\ Various 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.