Oracle nologging and Append Classic summary ____oracle

For logging's understanding that the table's log is always set to no it will not generate the log, in fact it is not the following are some explanations and experiments for logging.

Logging Introduction

You can perform the following actions in nologging mode :

1. Creation and ALTER (rebuild) of the index.
2. Table Bulk Insert (via/*+append/prompt using direct path insertion). or by Sql*loader Direct path loading). Table data generates a small amount of redo, but all index modifications generate a large number of redo (although the table does not generate a log, the index on this table generates redo.) ）。
3. LOB operations (updates to large objects do not have to generate a log).
4. Creates a table from the CREATE table as select.
5. Various ALTER TABLE operations, such as move and split.

On a archivelog-mode database, if nologging is used properly, it can speed up many operations because it can significantly reduce the amount of redo logs generated. Suppose you have a table that needs to be moved from one tablespace to another, and an operation that takes n hours to complete may require only N/2 hours. The proper use of this feature requires DBA involvement, or it must be communicated to people who are responsible for database backup and recovery (or any standby database). If the person does not know that this feature is used, it is important to think twice about the possibility of losing data if the media fails, or if the integrity of the standby database may be compromised.

Object Logging State query

Query the logging state of a table through this query SQL statement

SELECT T.table_name, t.logging
From User_tables T
WHERE t.table_name like '%test_futufares% ';

Logging test for Create and insert
Create table ... as select ... ... and insert into .... Select .... test

ways to change the logging state value:

ALTER TABLE table_name nologging/logging;

The following example is the source data in around 10,000, Create table ... as SELECT ... The test found a difference of about 2 seconds, especially in the large data volume with nologging create speed is indeed much faster.

The following is the test data volume of the INSERT statement is about 2 million, Test_futufares2 's logging whether it is in the state of Yes or no in fact, the insertion is the same speed

Through the above test in fact, the table in the nologging and logging state when inserting 2 million of the data is time-consuming, that is, DML is not to say no log but only in a particular case is not logged, such as using Sql*loader Direct loading and insert/*+append* /Option Direct path mount, which means that whether or not the nologging state DML operation is normal, the log will certainly be generated.

In nologging mode , database operations do not produce redo records in the following situations:
1, use Sql*load Direct load mode, do not use redo record
has been tested

2, with the Insert direct mode, that is, in the Append mode insert
has been tested

3. Create TABLE .... As select ....
has been tested

4, CREATE index
Create INDEX Test_futufares2_log on Test_futufares2 (Fare_kind,futufare_type) nologging;

To create an index to produce very few redo you have to create an index in the way that it is, to create an index in the above way, regardless of whether the log is in the nologging or logging state, a small number of redo logs are generated, or a lot of redo logs will be generated.
5. ALTER TABLE ... move partition
6. ALTER TABLE ... split partition
7. ALTER index ... split partition
8, ALTER index ... rebuild
9, ALTER index ... rebuild partition
10, INSERT, UPDATE, and DELETE on lobs into nocache nologging mode stored out of

Append Introduction

In the case of non-archive mode:

1. View the total amount of redo generated by the current session

table is in nologging state:

Sql> set timing on;
Sql>insert into Test_futufares2 SELECT * from Test_futufares;
2090220 rows Inserted
Executed in 36.25 seconds

Sql> SELECT a.name, b.value from V$mystat B, v$statname A WHERE a.statistic# = b.statistic# and a.name like '%redo size %';
NAME VALUE
--------------------------------------------------------------------------
Redo size 113495212

Sql>insert/*+append*/into the test_futufares2 SELECT * from Test_futufares;
2090220 rows Inserted
Executed in 9.062 seconds

Sql> SELECT a.name, b.value from V$mystat B, v$statname A WHERE a.statistic# = b.statistic# and a.name like '%redo s Ize% ';
name                                                        VALUE
--------------------------------------------------------------------------
Redo size                                                   113560764

Sql>select 113560764-113495212 from dual;
113560764-113495212
-------------------
65552

Table is in logging state:
The results of this test are virtually identical to those obtained from the nologging above, and are not posted.

In the case of archive mode:

table is in logging state:

Sql> INSERT into Test_futufares2 SELECT * from Test_futufares;
2090220 rows Inserted
Executed in 44.031 seconds

Sql> SELECT a.name, b.value from V$mystat B, v$statname A WHERE a.statistic# = b.statistic# and a.name like '%redo size %';
NAME VALUE
--------------------------------------------------------------------------
Redo size 113460280

Sql>insert/*+append*/into the test_futufares2 SELECT * from Test_futufares;
2090220 rows Inserted
Executed in 24.297 seconds

Sql> SELECT a.name, b.value from V$mystat B, v$statname A WHERE a.statistic# = b.statistic# and a.name like '%redo s Ize% ';
name                                                        VALUE
--------------------------------------------------------------------------
Redo size                                                  223253980

Sql> select 223253980-113460280 from dual;
223253980-113460280
-------------------
109793700

table is in nologging state:

Sql> INSERT/*+append*/into Test_futufares2 SELECT * from Test_futufares;
2090220 rows Inserted
Executed in 6.391 seconds

Sql> SELECT a.name, b.value from V$mystat B, v$statname A WHERE a.statistic# = b.statistic# and a.name like '%redo s Ize% ';
name                                                        VALUE
--------------------------------------------------------------------------
Redo size                                                  223576712

Sql> select 223576712-223253980 from dual;
223576712-223253980
-------------------
322732

2. View the global database redo build, which you can see through the V$sysstat view

Sql> Select Name,value from V$sysstat where name= ' redo size ';
NAME VALUE
--------------------------------------------------------------------------
Redo size 122314360

Summarize:
For a summary of nologging and append tests, the SQL statement that shows the SQL statement that adds/*+append*/to a large number of data insertion procedures can be used to generate a redo log indicating that it will save a lot of time, Of course, this may affect the backup so nologging to do a full database after loading the data.

Insert Append is not at any time can save time the following are some of the summary of the test:

In the first case:

The database is Archivelog state, in which case, even if you use insert append is not necessarily improve the insertion efficiency. But if you set the target table to nologging and then use the Insert Append, it will be quick.
In the second case:

Database is Noarchivelog state, if in this case the Insert method directly without adding append to the table to insert data, the size of the redo space occupied with the size of the Archivelog state is equivalent, Whether the table is nologging or not. However, if the use of Insert Append method, through the redo occupancy value you can find that regardless of whether the table is nologging, the size of the redo occupied is very small. Also explains: In the database for the Noarchivelog state, with the Insert Append method, if the table is not nologging, the system will automatically convert the table to nologging (that is, before the insert append executed, the first execution of an alter Table Arch1 nologging).
In the third case:

If there is an index on the table, the append way to add the records in batches does not reduce the number of redo produced on the index, and the number of redo on the index may be larger than the number of redo in the table. The direct path loading speed is much faster with insert append, but one thing to note is that adding "6" locks on the table when you insert append blocks all DML statements on the table, so use caution when you have a business running. Performing multiple insert append concurrently loading data on the same table does not necessarily increase the speed, because only one process can be loaded (exclusive lock) at a time. The idea was found on the Internet. For this question the brother may not be too understanding, For an Oracle database, as long as the DML, especially the insert operation, he would lock the table and be exclusive, except for Commit,rollback, and other DDL operations to release it, the lock would remain exclusive, causing the other DML operations to not operate properly. And it has nothing to do with the so-called append.

The following are about table schemas (logging/nologging), insert mode (append/noappend), Database run mode (archive/non-archive), redo log-generated relationships

Database schema	Table mode	Insert mode	Redo Generation
Archivelog	LOGGING	APPEND	Have redo
NO APPEND	Have redo
Nologging	APPEND	No redo
NO APPEND	Have redo
Noarchivelog	LOGGING	APPEND	No redo
NO APPEND	Have redo
Nologging	APPEND	No redo
NO APPEND	Have redo