Oracle Full-Time Journal (Supplemental Logging)

The Oracle Supplemental logging feature can be divided into the following types of functions: Minimum (Minimal), support for all fields (all), primary key (primary key), unique key support (unique), support for foreign keys ( Foreign key). field types, including Long,lob,long raw and set, cannot take advantage of a full-time log.

The minimum (Minimal) full-time log is turned on to enable the LOGMNR tool to support chained rows, cluster tables, and index organization tables. The following SQL can be used to check whether the minimum completion log is turned on:
SELECT supplemental_log_data_min from V$database;

If the result returns Yes or implicit, the minimum full-time log is turned on, and the minimum full-day log is turned on by default when using All,primary,unique or foreign (that is, the check result is implicit).

In general, we enable a full-time log of primary and unique keys when using a logical repository, and sometimes there may be no primary key, unique key, or unique index on the table; We summarize the performance of Oracle in this case with the following experiment.

First set up the relevant test table:
ALTER DATABASE Add supplemental log data (primary Key,unique index) columns;

CREATE TABLE test (t1 int, t2 int, t3 int, T4 int);

ALTER TABLE TEST ADD constraint Pk_t1 primary key (T1); – Add primary Key

Then use a loop to insert a certain amount of data

Update test set t2=10;   Commit -Update data

Using the LOGMNR tool to analyze previous operations, you can see that the SQL form recorded in Redo is as follows:
Update "SYS". " TEST "Set" T2 "= ' where" T1 "= '" and "T2" = ' n ' and ROWID = ' aaamisaabaaaohiaa/';

The WHERE clause records the primary key value, the value of the modified field, and the rowid of the original row.

Now let's take a look at the primary key on the original table.
ALTER TABLE test drop constraint pk_t1;

Update test set t2=11;   Commit -Update data

Using LOGMNR analysis, you can see that the SQL records in redo are as follows:

Update "SYS". " TEST "Set" T2 "= ' one ' where" T1 "= ' 1 ' and" T2 "= '" and "T3" = ' 3 ' and "T4" = ' 4 ' and ROWID = ' aaamisaabaaaohiaaa ';

When there is no primary key, all column values and rowID are recorded after the WHERE clause.
The performance of the following experiments in the presence of a unique index

Create unique index pk_t1 on test (T1);

Update test set t2=15; Commit

Using LOGMNR analysis, you can see that the SQL records in redo are as follows:

Update "SYS". " TEST "Set" T2 "= '" where "T1" = ' 9 ' and "T2" = ' one ' and ' T3 ' = ' one ' and ' T4 ' = ' n ' and ROWID = ' aaamisaabaaaohiaai ';

The above is a case where the T1 column has a unique index but does not qualify not NULL, we add the NOT NULL limit below

ALTER TABLE test modify T1 NOT null;

Update test set t2=21; Commit

Using LOGMNR analysis, you can see that the SQL records in redo are as follows:

Update "SYS". " TEST "Set" T2 "= '" where "T1" = ' 2 ' and "T2" = ' "and ROWID = ' Aaamisaabaaaohiaab ';

As shown in SQL above, all columns and ROWID are still logged after the WHERE clause where a unique index exists, consistent with the presence of a primary key in the presence of a unique index and non-null constraint.

When the number of columns on a table is large and there are no primary keys or unique indexes and non-null constraints, turning on a full-time log may result in a significant increase in the amount of redo logs.
First create a table with 250 columns:

Drop table test;

CREATE TABLE Test (

T1 VARCHAR2 (5),

T2 VARCHAR2 (5),

T3 varchar2 (5),

T4 varchar2 (5), ... t250 varchar2 (5))

INSERT into test values (' Test ', ' test ' ...); Commit – Fill 255 columns in the data

ALTER DATABASE drop supplemental log data (primary Key,unique index) columns; – Close the full-time log

Set autotrace on;

Update test set t2= ' bzzzz ' where t1= ' test '; Commit

You can see from the automatic tracking information that this update produces 516 of the redo amount.

ALTER DATABASE Add supplemental log data (primary Key,unique index) columns;– re-open full-time log

Update test set t2= ' FSDSD ' where t1= ' test ';

The tracking information shows that 3044 of the redo amount is generated.

It can be divided into database-level and table-level depending on the scope of the full-time log. Table-level full-time records can be divided into conditional and unconditional. Conditional table-level records only work when a specific column is updated, and there is less use for conditional table-level full-time logs, which we do not discuss here.

Let's look at the specific performance of the unconditional limit table-level full-time log:
ALTER DATABASE drop supplemental log data (primary Key,unique index) columns;

ALTER TABLE test add supplemental log data (primary Key,unique index) columns;

Update test set t2= ' zzzzz '; Commit

Use the LOGMNR tool to view SQL in redo:
Update "SYS". " Test "Set" T2 "= ' zzzzz ' where" T1 "= ' Test ' and" T2 "= ' AAAAA ' and ' T3 ' = ' test ' ....

You can see that all column values are included after the WHERE clause.

Delete test; Commit

Use the LOGMNR tool to view SQL in redo:

Delete from "SYS". " Test ' where ' T1 ' = ' test ' and ' T2 ' = ' zzzzz ' and ' T3 ' = ' test ' and ' T4 ' = ' test ' and ' T5 ' ...

The delete operation also includes all column values after the WHERE clause.

We can also establish a specific set of full-time records for the fields on the table to reduce the appearance of the column values after the WHERE clause.

ALTER TABLE test drop supplemental log data (primary Key,unique index) columns; – Close the original full-time log on the table

ALTER TABLE test add supplemental Log Group TEST_LGP (T1, t2,t3,t4,t5,t6,t12,t250) always; – Create a full-complement log group

Update test set t2= ' XXXXX '; Commit

Use the LOGMNR tool to view SQL in redo:

Update "SYS". " Test "Set" T2 "= ' XXXXX '" where "T1" = ' test ' and ' T2 ' = ' test ' and ' T3 ' = ' test ' and ' T4 ' = ' test ' and ' T5 ' = ' test ' and ' T6 "= ' Test ' and" T12 "= ' test ' and ' T250 ' = ' test ' and ROWID = ' aaamieaabaaaohnaaa ';

The user-specified field values in the update operation are displayed correctly in the Redo log as shown above.

Delete test;

Use the LOGMNR tool to view SQL in redo:

Delete from "SYS". " Test "where" T1 "= ' Test ' and" T2 "= ' XXXXX ' and" T3 "= ' Test ' ...

The delete operation still retains all column values in the Redo log.

For tables with more fields, we can specify the set of full-time records on the table to reduce the redo log that results from the update operation, but not for the delete operation, when we are able to guarantee data uniqueness in multiple columns and NOT NULL (that is, applying a conceptual primary key).

Oracle Full-Time Journal (Supplemental Logging)