Unlogged table in Oracle

Question

I'm studying PostGreSQL, and I saw an example of unlogged table. Is there a similar resource in Oracle? I have a little extractor in Oracle, that diary fills a table then makes a text file. After that, this table is cleaned, but not dropped. I understand that this "temporary table" could be unlogged, because it is not a business table at all.

Answer 1

Oracle tables can be created as NOLOGGING. This is similar to the PostgreSQL UNLOGGED option but I'm sure there are many implementation differences.

Even if the table is created as NOLOGGING only specific operations will use a direct-path insert (that is, an INSERT that writes directly to the datafile and does not generate much REDO or UNDO). And the DML generally must use the APPEND hint like below. You can tell if direct-path writes are used if you see LOAD AS SELECT.

SQL> create table test1(a number) nologging;

Table created.

SQL> explain plan for insert /*+ append */ into test1 select 1 from dual;

Explained.

SQL> select * from table(dbms_xplan.display);
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 2781518217

----------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD AS SELECT                  | TEST1 |       |            |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |       |     1 |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL                     |       |     1 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

There are many strange limitations on direct-path writes. It's common to make a minor mistake and end up with conventional inserts. In the example below, the hint does not have the correct syntax. There is no error or warning, only the explain plan shows LOAD TABLE CONVENTIONAL.

SQL> explain plan for insert /* append */ into test1 select 1 from dual;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 1388734953

--------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     1 |     2   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | TEST1 |       |            |          |
|   2 |   FAST DUAL              |       |     1 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

9 rows selected.