6 Replies Latest reply: Jan 8, 2009 1:46 AM by Karthick_Arp RSS

    Richard Foote - Are you wrong or am i confused :-(

    Karthick_Arp
      In response to Burleson in the following thread

      The specified item was not found.

      You talked about how index clean out happens when a subsequent insert happens in the same index block where the deleted index entry exists. Are you mean to say that the index clean out happens in the same transaction? If so i think you could be wrong.

      I posted this question to Tom Kyte and he clearly says with an example this

      "deleted index space *cannot* be reused in a single transaction."

      You can see my post here

      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969#1398045100346039707

      So can you please explain on this.

      Note: I had to post a new thread as the reply button has bean disabled in the above thread which had your reply.

      Thanks,
      Karthick.
        • 1. Re: Richard Foote - Are you wrong or am i confused :-(
          581825
          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!
          • 2. Re: Richard Foote - Are you wrong or am i confused :-(
            Karthick_Arp
            Ok i could be totally misunderstanding the explanation given by Richard. Lets see the following example quoted by him.
            So for example, if we update an index entry "BURLESON" with a new value of "ZIGGY", the index entry with >"BURLESON" is only marked as deleted while the new value of "ZIGGY" is inserted in likely another index leaf block.

            But But But, if we were to insert a new index entry called (or update something to) "BOWIE" and it happens to >be inserted in the same index leaf block that currently contains the deleted index entry "BURLESON", the deleted >entry (and any others that may exist in the leaf block) is cleaned out automatically by Oracle.
            In this i will tell what i inferred (which could be totally incorrect)

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

            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.

            From your explanation it occurs that my inference is wrong.

            Thanks,
            Karthick.
            • 3. Re: Richard Foote - Are you wrong or am i confused :-(
              581825
              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.
              • 4. Re: Richard Foote - Are you wrong or am i confused :-(
                Karthick_Arp
                Thanks to Tom and You.

                My misinterpretation has been clarified.

                So the fact is that It requires an insert of a different transaction to clean the deleted index entry and not that of the same transaction.

                Note: Richard could be correct, it must be just my misinterpretation. Any way i am a happy man now ;)

                Thanks,
                Karthick.
                • 5. Re: Richard Foote - Are you wrong or am i confused :-(
                  311441
                  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/
                  • 6. Re: Richard Foote - Are you wrong or am i confused :-(
                    Karthick_Arp
                    Thanks Richard for taking the time to clarify and for giving the additional information about the unique index.