Why would you think he "meant" to say 'in the same transaction'?

As assumptions go, it's a little bizarre. Since I have made the same point myself over the past nine years, I can tell you that if I'd ever meant "in the same transaction", I'd have said as much. And I suspect the same is true for Richard.

The point I've made before is that indexes are commonly used to speed up OLTP systems -and in such systems, you commonly see small transactions that drop in and out of the data very quickly. The common pattern would be an update, commit, finish and done. And then moments later, from another session, insert. The point is that the spaced 'wasted' by the update doesn't need a rebuild to be reclaimed. The subsequent insert will do it for you, if it hits the same block.

Maybe it's good to be explicit about the 'not in same transaction' bit, but quite frankly it's never occurred to me in all this time that anyone would assume that wasn't what was meant anyway!

When an index entry BURLESON is updated with ZIGGY then BURLESON is marked as deleted and ZIGGY is inserted likely in another leaf block.

Correct, I'd have said.

But if an index entry BURLESON is updated with BOWIE then BURLESON is marked as deleted and there is a high possibility for BOWIE to get inserted in the same leaf block as that of BURLESON. In that case the deleted entry gets cleaned up.

Ah, I now see the problem.

Here, Richard is saying that the insert CAUSED by the original update does its own cleanup. That is indeed not true, I think. It takes a second transaction, as Tom told you, to effect the cleanup. An update can't clean out its own deleted leaf rows:

SQL> select * from orig;
 
O_NAME     O_CLASS
---------- ----------
Abbey      Low
Harry      NEW
Abbey      Low
Abbey      Low
 
SQL> alter index iorig rebuild;
 
Index altered.
 
SQL> analyze index iorig validate structure;
 
Index analyzed.
 
SQL> select del_lf_rows from index_stats;
 
DEL_LF_ROWS
-----------
          0
 
SQL> update orig set o_name='andy' where o_name='Abbey';
 
3 rows updated.
 
SQL> analyze index iorig validate structure;
 
Index analyzed.
 
SQL> select del_lf_rows from index_stats;
 
DEL_LF_ROWS
-----------
          3
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index iorig validate structure;
 
Index analyzed.
 
SQL> select del_lf_rows from index_stats;
 
DEL_LF_ROWS
-----------
          3

One transaction, updaing 'A' records to 'a' records (and it's a small enough table so I know it's the same index block involved anyway), and deleted leaf rows are left there. But as soon as the new transaction starts:

SQL> insert into orig values ('Arlen','NONE');
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> analyze index iorig validate structure;
 
Index analyzed.
 
SQL> select del_lf_rows from index_stats;
 
DEL_LF_ROWS
-----------
          0

All cleanups are done. I think Richard just mis-spoke a little. But I'll shut up and let him clarify if I've now misunderstood the point he was making.

Hi Karthick

No, I meant in a subsequent transaction can clean out any deleted entries. Sorry if I confused you.

We've just had a big discussion on what happens if you delete an entire table and reinsert within the same transaction

Note though that for unique indexes, a deleted index entry can be reused if inserted again within the same transaction. That was also discussed recently.

Cheers

Richard Foote
http://richardfoote.wordpress.com/