How to Slash Redo Log Generation and Logical Reads in Oracle UPDATE Statements

The customer observed that an hourly UPDATE on tb_test_log (setting object_id=1 where owner='SYS') generated massive redo logs and logical reads, even though most qualifying rows already had object_id=1. ADBA and LOGMINER confirmed the issue.

Test environment : Oracle 12.1.0.2.0 on CentOS 6.6. A test table tb_test_log was created as a copy of dba_objects and duplicated several times to reach roughly 100 MB (≈109 MB). Approximately 46 % of rows satisfy owner='SYS'.

Baseline measurements

Running the original statement update tb_test_log set object_id=1 where owner='SYS'; produced redo size around 100 MB and logical reads around 700 k per execution, as measured with v$statname and v$sesstat in two separate sessions.

Optimization 1 – Add object_id!=1 filter

update tb_test_log set object_id=1 where owner='SYS' and object_id!=1;
commit;

After applying this predicate, redo size fell to roughly 0.8 MB and logical reads to about 22 k per execution, a reduction of more than 99 % in redo and 97 % in reads.

Optimization 2 – Use decode filter and composite index

Created an index to support the new predicate:

create index idx_tb_test_log_01 on tb_test_log(owner,decode(object_id,1,null,'1')) tablespace users;

Updated statement:

update tb_test_log set object_id=1 where owner='SYS' and decode(object_id,1,null,'1')='1';
commit;

With the index in place, redo size dropped to about 0.4 MB and logical reads to roughly 12 k per execution, further improving efficiency.

Conclusion : Adding a selective predicate that excludes rows already having the target value, and optionally using a decode expression with a matching composite index, can reduce redo log generation by two orders of magnitude and dramatically lower logical reads. The same approach can be applied to similar UPDATE or DELETE statements.

Filter out rows that already meet the update condition (e.g., object_id!=1) to drastically lower redo log volume.

Use a decode -based predicate together with a supporting index to further cut both redo generation and logical reads.

The technique is applicable to analogous UPDATE/DELETE workloads.