Managing Tables: Logging versus Nologging

By Steve Callan

When creating large tables (large referring to the amount of data), you may want to consider creating them with the nologging option. If the data is of a transient nature, that is, the table is used for staging and other manipulation will transform it into yet another table, do you really need to generate redo log activity or entries? On the other hand, what if the new table is created using CTAS (create table as select)? If the new CTAS-created table includes the nologging option, what are the implications for future data manipulation language (insert, update, or delete) operations?

The main question about NOLOGGING is this: does creating a table with the nologging option mean there is no logging (i.e., generation of redo entries) ever, or just that the initial creation operation has no logging, but that DML down the road generates logging? "Managing Tables," Chapter 15 of Oracle® Database Administrator's Guide 10g Release 2 (10.2), covers options you may want to consider when creating tables.

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

Rule number one with respect to data is to never put yourself into an unrecoverable situation. The importance of this guideline cannot be stressed enough, but it does not mean that you can never use time saving or performance enhancing options. How and when can the NOLOGGING option be employed?

As already mentioned, one way is to use it during CTAS operations. Let's take a look at creating a large (relatively speaking) table using CTAS with and without NOLOGGING. The example table is SALES in the SH sample schema.

SQL> set timing on
SQL> create table sales_logging as
  2  select * from sales;
Table created.
Elapsed: 00:00:25.24
SQL> create table sales_nologging NOLOGGING as
  2  select * from sales;
Table created.
Elapsed: 00:00:06.59

For just over 900,000 rows, the time difference is around 18 seconds. Let's ramp up the size to over 10 million and compare the times. The demo table is SALES_x10 and was created using NOLOGGING, and then built up by selecting * from SALES in repeated insert statements.

SQL> create table sales_x10_logging as
  2  select * from sales_x10;
Table created.
Elapsed: 00:03:19.70
SQL> create table sales_x10_nologging NOLOGGING as
  2  select * from sales_x10;
Table created.
Elapsed: 00:01:29.80

The time difference now is close to two minutes. As a rough estimate using extrapolation, the times for creating a 100 million row table are 32 and 15 minutes, respectively. In a "quick backup/fix some problem right now" scenario, that 15-plus minute difference can be huge in terms of significance.

Now that the new table is created, how does the initial NOLOGGING option impact DML operations? Is there logging or not? Here is a simple test: delete some rows, rollback, and see if the counts are the same.

SQL> select count(*) from sales_nologging;
  COUNT(*)
----------
    918843
SQL> delete from sales_nologging
  2  where rownum < 100000;
99999 rows deleted.
SQL> select count(*) from sales_nologging;
  COUNT(*)
----------
    818844
SQL> rollback;
Rollback complete.
SQL> select count(*) from sales_nologging;
  COUNT(*)
----------
    918843

This result is also borne out by what is stated in the documentation: "The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by theNOLOGGING attribute of the table and generate redo."

What happens at the tablespace level? The answer is: it depends. If the tablespace has logging enabled (which is the default), then objects created with NOLOGGING afterwards will stay NOLOGGING, but objects created when logging was in effect will stay that way. Here is the current logging status of our SALES-related tables:

TABLE_NAME                     LOG
------------------------------ ---
SALES
SALES_LOGGING                  YES
SALES_NOLOGGING                NO
SALES_X10                      NO
SALES_X10_LOGGING              YES
SALES_X10_NOLOGGING            NO

Why doesn't SALES have a logging status? The SALES table is partitioned, so if you view the logging status via USER_TAB_PARTITIONS, each partition's logging status will be displayed. Let's alter the USERS tablespace to NOLOGGING (step not shown below), create a new table, and check its logging status. Note: the SALES_TRANSACTION_EXT table was manually deleted from the list below, but does appear in the SQL*Plus output).

SQL> CREATE TABLE SALES_NO_TS_LOGGING
  2  (PROD_ID NUMBER NOT NULL,
  3  CUST_ID NUMBER NOT NULL,
  4  TIME_ID DATE NOT NULL,
  5  CHANNEL_ID NUMBER NOT NULL,
  6  PROMO_ID NUMBER NOT NULL,
  7  QUANTITY_SOLD NUMBER NOT NULL,
  8  AMOUNT_SOLD NUMBER NOT NULL);
Table created.
SQL> select table_name, logging
  2  from user_tables
  3  where table_name like 'SALES%';
TABLE_NAME                     LOG
------------------------------ ---
SALES
SALES_LOGGING                  YES
SALES_NOLOGGING                NO
SALES_NO_TS_LOGGING            NO
SALES_X10                      NO
SALES_X10_LOGGING              YES
SALES_X10_NOLOGGING            NO

Can we force the SALES_NO_TS_LOGGING table to be logging in a NOLOGGING tablespace? As shown below, the answer is yes.

SQL> alter table sales_no_ts_logging LOGGING;
Table altered.
SQL> select table_name, logging
  2  from user_tables
  3  where table_name like 'SALES_NO%';
TABLE_NAME                     LOG
------------------------------ ---
SALES_NOLOGGING                NO
SALES_NO_TS_LOGGING            YES

Clarifying what NOLOGGING means

Let's suppose a table is created using the NOLOGGING option, regardless of how NOLOGGING is being invoked (in a CREATE statement using NOLOGGING, or in a tablespace with NOLOGGING set). What is the end result of creating a table, inserting data, committing, followed by a delete statement and a rollback statement? Does NOLOGGING mean the DML is not recorded and that you cannot rollback because there was nothing logged in the redo logs?

SQL> create table test
  2  (id number) nologging;
Table created.
SQL> insert into test values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> delete from test;
1 row deleted.
SQL> rollback;
Rollback complete.
SQL> select * from test;
        ID
----------
         1

The answer is no, that is not what NOLOGGING means. To reiterate what was stated before:

"The NOLOGGING clause also specifies that subsequent direct loads using SQL*Loader and direct load INSERT operations are not logged. Subsequent DML statements (UPDATE, DELETE, and conventional path insert) are unaffected by the NOLOGGING attribute of the table and generate redo."

In Closing

As demonstrated, using the NOLOGGING option can be a time saver, but it can also put you at risk if you do not use it wisely. If you create a table with NOLOGGING, but cannot afford to lose the data (which implicitly means you need the table it is stored in), the first step after the data load is complete is to take a backup. If a good part of your loading data into a database work revolves around using SQL*Loader loading data into stage tables, make the tables (or tablespace) NOLOGGING and save yourself some time.

Source :

http://www.databasejournal.com/features/oracle/article.php/3631361/Managing-Tables-Logging-versus-Nologging.htm