we do not maintain indexes if the data values do not change.
That can be observed by doing an update of a column from one value to another, measuring the db block gets (current mode, update style gets) and then modifying the column to its current value, back and forth.
eg:
ops$tkyte%ORA10GR2> create table t
2 as
3 select 1 x from all_objects;
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
255500 db block gets
176 consistent gets
34191460 redo size
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
51556 db block gets
12535632 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
51688 db block gets
12541488 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
Statistics
----------------------------------------------------------
255415 db block gets
34187164 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
Statistics
----------------------------------------------------------
51544 db block gets
12536032 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
ops$tkyte%ORA10GR2> create table t
2 as
3 select 1 x from all_objects;
Table created.
ops$tkyte%ORA10GR2> create index t_idx on t(x);
Index created.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
255500 db block gets
176 consistent gets
34191460 redo size
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
51556 db block gets
12535632 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 2;
50388 rows updated.
Statistics
----------------------------------------------------------
51688 db block gets
12541488 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
Statistics
----------------------------------------------------------
255415 db block gets
34187164 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
Statistics
----------------------------------------------------------
51544 db block gets
12536032 redo size
50388 rows processed
ops$tkyte%ORA10GR2> commit;
Commit complete.
ops$tkyte%ORA10GR2> update t set x = 1;
50388 rows updated.
see the radical decrease in db block (current mode, update mode) gets and redo size?
we skipped the index maintenance