Misconception about direct-path insert

I had a misconception about direct-path insert. I thought it would generate minimized redo by design. Actually, direct-patch insert can be performed with both logging (by default) and nologging mode. Oracle doc is very clear about this.

Now it comes to how to perform direct-path insert with nologging. I thought it would work by just adding 'nologging' after the table name in the SQL statement, similar to the case of CTAS. It turned out to be not true.

Here was my test to insert 1 million rows to a table and measure the redo generated in each case:

Case 1: Conventional insert show redo size about 70M

DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                           63004

Elapsed: 00:00:03.06
DB9i>
DB9i> insert into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:57.00
DB9i> commit;

Commit complete.

Elapsed: 00:00:05.43
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                        71015272       70,952,268

Case 2: Direct-path insert generate same amount of redo

DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                       142548540

Elapsed: 00:00:06.04
DB9i>
DB9i> insert /*+append */ into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:17.10
DB9i> commit;

Commit complete.

Elapsed: 00:00:06.37
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                       213379400       70,830,860

Note: By adding nologging keyword as follows, nothing changes, same amount of redo:

insert /*+append */ into t nologging select * from big_table a where rownum <= 1000000;


Case 3. To enable direct-path insert with nologging, we have to specify nologging at table level:

DB9i> truncate table t;

Table truncated.

Elapsed: 00:00:08.40
DB9i>
DB9i> alter table t nologging;

Table altered.

Elapsed: 00:00:06.29
DB9i>
DB9i> @@mystat "redo size"
DB9i> set echo off

NAME                                VALUE
------------------------------ ----------
redo size                       284863656

Elapsed: 00:00:07.37
DB9i>
DB9i> insert /*+append */ into t select * from big_table a where rownum <= 1000000;

1000000 rows created.

Elapsed: 00:00:08.17
DB9i> commit;

Commit complete.

Elapsed: 00:00:08.48
DB9i>
DB9i> @@mystat2
DB9i> set echo off

NAME                                    V DIFF
------------------------------ ---------- ------------------
redo size                       285145076          281,420

Note: Bear in mind the recovery scenario, this operation is not recoverable. After performing nologging operation, generally we should do a backup.

I should mention that the above results were obtained in a 9i database operated in ARCHIVELOG mode. If in a NOARCHIVELOG mode database, conventional inserts still generate same amount of redo. However, by just adding the hint 'append', we can see redo size dropped to around 280K. After all we do not expect a complete recovery for a NOARCHIVELOG database. So be careful about in which kind of databases the tests are being done.