Skip to Main Content
  • Questions

  • Index maintained on update if no change?

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Tom Kyte

Thanks for the question, Steve.

Asked: April 30, 2010 - 1:46 pm UTC

Last updated: May 03, 2010 - 3:17 pm UTC

Version: 10.2.0.3

Viewed 1000+ times

You Asked


Hi Tom,

If an update statement changes an indexed the column, any index using that column must be maintained. However, if the "new" value of the indexed column is the same as the current value (no net change), is there any wasted time trying to maintain an index that won't really change?

A popular application has the peculiar habit of including the keys in both the Set and Where clause when using the "preferred" built-in method for issuing individual updates. Time wasted maintaining indexs with those columns could add up when processing tens or hundreds of thousands of rows. There is an option to issue the actual SQL instead, so it seems worth exploring.

Thanks!



and Tom said...

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

Rating

  (2 ratings)

We're not taking comments currently, so please try again later if you want to add a comment.

Comments

Memory lane

Stew Ashton, May 04, 2010 - 3:44 pm UTC


This reminded me of the time I asked practically the same question and you proved the index wasn't touched two different ways. The "alter tablespace offline" proof blew me away. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6618304976523#548767100346133571

Excellent as always

Steve Scheifler, May 05, 2010 - 7:41 am UTC

The answer, and the explanation of how to demonstrate it.

Thanks!
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.