Oracle nologging research, oraclenologging

Oracle nologging research, oraclenologging

Anyone familiar with oracle knows that by setting oracle's nologging option, some operations can be completed quickly, and the risk is that database backup may fail. There are many nologging operations that can be used. Collect the following information:

1. Create and ALTER indexes ).
2. Batch INSERT of tables (with/* + append */, you are prompted to use "direct path INSERT". Or use SQL * Loader to load directly ). A small amount of redo is generated for table data, but a large amount of redo is generated for all index modifications (although the table does not generate logs, the index on this table generates a redo !).
3. Lob operation (you do not need to generate logs for updates to large objects ).
4. create table as select to create a table.
5. Various alter table operations, such as move and split.

6. dml operations always generate redo, except for the lob field (related to the undo mode of the lob field)

Next, let's use it:

First, in archive mode:

SQL> show userUSER is "YUE"SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size                   684SQL> create table t2 as select * from t1;Table created.SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size               8573344SQL> create table t3 nologging  as select * from t1;Table created.SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size               8725596

Let's take a look at the non-archive mode:

SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size                  1392SQL> create table t4 as select * from t1;Table created.SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size                191000SQL> create table t5 nologging as select * from t1;Table created.SQL> @redo.sqlSTATISTIC# USERNAME                       NAME                      VALUE---------- ------------------------------ -------------------- ----------       178 YUE                            redo size                297176

In non-archive mode, oracle does not generate redo operations regardless of whether nologging is specified.

So what are the commonalities of all the above nologging operations?

I think that all these operations generate new data blocks or directly cover all the content of old data blocks. For example, the insert append operation directly inserts data on the high-water level line. This explains why the index corresponding to the insert append operation generates a redo, because the modification to the index is neither creating a block nor overwriting all the content of the old block.

DML operations generate logs in nologging mode because dml operations do not necessarily involve new blocks or cover all the content of old blocks. Lob dml does not generate redo because the lob dml operation always saves the edited data on the new block.