LOGGING or NOLOGGING, that is the question – Part IV

By Francisco Munoz Alvarez Oracle ACE

Disabling Redo Generation (NOLOGGING)  

The NOLOGGING attribute tells the Oracle that the operation being performed does not need to be recoverable in the event of a failure. In this case Oracle will generate a minimal number of redo log entries in order to protect the data dictionary, and the operation will probably run faster. Oracle is relying on the user to recover the data manually in the event of a media failure. 

Logging can be disabled at the table level or the tablespace level. If it is done at the tablespace level then every newly created index or table in this tablespace will be in NOLOGGING mode (You can have logging tables inside a NOLOGGING tablespace). A table or an index can be created with NOLOGGING mode or it can be altered using ALTER TABLE/INDEX NOLOGGING. It is important to note that just because an index or a table was created with NOLOGGING does not mean that redo generation has been stopped for this table or index. NOLOGGING is active in the following situations and while running one of the following commands but not after that.  This is a partial list: 

  • DIRECT LOAD (SQL*Loader)
  • DIRECT LOAD  INSERT (using APPEND hint)
  • CREATE TABLE … AS SELECT
  • CREATE INDEX
  • ALTER TABLE MOVE
  • ALTER TABLE … MOVE PARTITION
  • ALTER TABLE … SPLIT PARTITION
  • ALTER TABLE … ADD PARTITION (if HASH partition)
  • ALTER TABLE … MERGE PARTITION
  • ALTER TABLE … MODIFY PARTITION
    • ADD SUBPARTITON
    • COALESCE SUBPARTITON
    • REBUILD UNUSABLE INDEXES
  • ALTER INDEX …
    SPLIT PARTITION
  • ALTER INDEX … REBUILD
  • ALTER INDEX … REBUILD PARTITION

Logging is stopped only while one of the commands above is running, so if a user runs this: 

  • ALTER INDEX new_index NOLOGGING.

The actual rebuild of the index does not generate redo (all data dictionary changes associated with the rebuild will do) but after that any DML on the index will generate redo this includes direct load insert on the table which the index belongs to.

Here is another example to make this point more clear: 

CREATE TABLE new_table_nolog_test NOLOGGING(….); 

All the following statements will generate redo despite the fact the table is in NOLOGGING mode: 

  • INSERT INTO new_table_nolog_test …,
  • UPDATE new_table_nolog_test SET …,
  • DELETE FROM new_table_nolog_test .. 

The following will not generate redo (except from dictionary changes and indexes):

  • INSERT /*+APPEND+/ …
  • ALTER TABLE new_table_nolog_test MOVE …
  • ALTER TABLE new_table_nolog_test MOVE PARTITION … 

Consider the following example: 

SQL> select name,value from v$sysstat where name like ‘%redo size%’; 

NAME                                                                    VALUE 

——————————————————   ———- 

redo size                                                                27556720 

SQL> insert into scott.redo1 select * from scott.redotesttab; 

50000 rows created. 

SQL> select name,value from v$sysstat where name like ‘%redo size%’;  

NAME                                                                    VALUE  

————————————————- ———- 

redo size                                                               28536820 

SQL> insert /*+ APPEND */ into scott.redo1 select * from scott.redotesttab; 

50000 rows created. 

SQL> select name,value from v$sysstat where name like ‘%redo size%’; 

NAME                                                                    VALUE 

————————————– ———- 

redo size                                                               28539944 

You will notice that the redo generated via the simple insert is “980100″ while a direct insert generates only “3124″.

To activate the NOLOGGING for one of the ALTER commands above add the NOLOGGING clause after the end of the ALTER command.  

For example: 

ALTER TABLE new_table_nolog_test MOVE PARTITION parti_001 TABLESPACE new_ts_001 NOLOGGING; 

The same applies for CREATE INDEX but for CREATE TABLE the NOLOGGING should come after the table name.  

Example: 

CREATE TABLE new_table_nolog_test NOLOGGING AS SELECT * FROM big_table; 

“It is a common mistake to add the NOLOGGING option at the end of the SQL (Because oracle will consider it an alias and the table will generate a lot of logging).” 

To user Direct Path Load in SQL*Loader you must run the $ORACLE_HOME/rdbms/admin/catldr.sql script before your first sqlldr is run in direct path mode. To run sqlldr in direct path mode use direct=true.  

Note: Even though direct path load reduces the generation of redo, it is not totally eliminated. That’s because those inserts still generate undo which in turn generates redo.

Wait for next part, I will talk about how to reduce Log Generation, without use NOLOGGING.  

One thought on “LOGGING or NOLOGGING, that is the question – Part IV

  1. Pingback: ora-click.com

Leave a Reply