12 Replies Latest reply: Apr 17, 2013 9:14 PM by Girish Sharma RSS

    Which DML generates the most UNDO ?

    Max
      Version : 11.2.0.3

      Which DML generates the most UNDO ?

      INSERT
      DELETE
      UPDATE
      DELETE


      And why ?
        • 1. Re: Which DML generates the most UNDO ?
          DK2010
          Hi,

          You can find more detail on , just go through once, take only 10 min :)

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5663330700346736253
          • 2. Re: Which DML generates the most UNDO ?
            Aman....
            Why do you want to know? Exactly what's the question?

            Aman....
            • 3. Re: Which DML generates the most UNDO ?
              Girish Sharma
              Undo generation quantity (DESC order) :

              Delete
              Update
              Insert

              Page 323 Expert Oracle Database Architecture 9I& 10G Prog. By Thomas Kyte

              Regards
              Girish Sharma
              • 4. Re: Which DML generates the most UNDO ?
                EdStevens
                Girish Sharma wrote:
                Undo generation quantity (DESC order) :

                Delete
                Update
                Insert

                Page 323 Expert Oracle Database Architecture 9I& 10G Prog. By Thomas Kyte

                Regards
                Girish Sharma
                I don't have the cited reference available, but I suspect you must be overlooking some caveats. It can't be that simplistic.

                - delete a single row
                - update 100,000 rows
                - insert 100,000,000 rows.

                does the DELETE still generate the most undo?

                At the source cited by DK2010, Tom Kyte clearly prefaced his explanation with "it depends".

                Edited by: EdStevens on Apr 17, 2013 7:04 AM
                • 5. Re: Which DML generates the most UNDO ?
                  rp0428
                  >
                  I don't have the cited reference available, but I suspect you must be overlooking some caveats. It can't be that simplistic.

                  - delete a single row
                  - update 100,000 rows
                  - insert 100,000,000 rows.

                  does the DELETE still generate the most undo?
                  >
                  You at least need to compare like quantities. It's easy to find some nit-picking fault with almost any answer by contriving extreme examples.

                  Just create a custom functional index that emails the pope and waits for a response and doing anything with a single row could take a very long time even though Easter is over and you'd think he would have a lot of free time.

                  Of course most everything can be captioned with 'it depends' but given what OP posted the 'delete, update, insert' is the proper answer.

                  Per Expert Oracle Database Architecture Oracle Database 9i, 10g, and 11g by Tom Kyte Page 332
                  >
                  What Generates the Most and Least Undo?

                  This is a frequently asked but easily answered question. The presence of indexes (or the fact that a table is an index-organized table) may affect the amount of undo generated dramatically, as indexes are complex data structures and may generate copious amounts of undo information.

                  That said, an INSERT will, in general, generate the least amount of undo, since all ORacle needs to record for this is a rowid to "delete".
                  . . .
                  An UPDATE is typically second in the race (in most cases).
                  . . .
                  A DELETE will, in general, generate the most undo.
                  • 6. Re: Which DML generates the most UNDO ?
                    Mark D Powell
                    RP, I agree with Ed. The response should have included some qualifying statement like "when dealing with the same row of data" to be more specific to ensure the OP understands the limitations of the answer.

                    IMHO -- Mark D Powell --
                    • 7. Re: Which DML generates the most UNDO ?
                      rp0428
                      >
                      RP, I agree with Ed. The response should have included some qualifying statement like "when dealing with the same row of data" to be more specific to ensure the OP understands the limitations of the answer.
                      >
                      Girish's response did have 'some qualifying statement. He provide the reference to the expert resource that he used.

                      Everything is subject to interpretation. Sometimes you just need to use common sense. I don't consider it common sense to interpret OPs question to be asking about possibilities where one of those ops is for ONE row and one is for 100 million rows. That is just ridiculous on the face of it, IMHO. :D

                      Let's assume this is a test question:
                      >
                      Which DML generates the most UNDO ?

                      A. INSERT
                      B. DELETE
                      C. UPDATE
                      >
                      Remember - this is a TEST. On tests you need to select 'the best, or most correct' answer.

                      I would choose 'B. DELETE' since I consider that to be the 'most correct' given the information available.

                      Why don't you and Ed tell us 'which of those three possible answers' you would choose?
                      • 8. Re: Which DML generates the most UNDO ?
                        Mark D Powell
                        The OP's post includes DELETE as an option twice, so obviouly it is the second DELETE and not the first that is the correct answer. LOL.

                        The referenced material may contain some qualifiers but that is no reason to not include some qualifying information in the response.

                        IMHO -- Mark D Powell --
                        • 9. Re: Which DML generates the most UNDO ?
                          Girish Sharma
                          I don't have the cited reference available,
                          But, I have [url http://books.google.co.in/books?id=_JVb4S-KcCcC&pg=PA323&lpg=PA323&dq=which+dml+generates+more+undo&source=bl&ots=95or3g5elD&sig=_QpMvybc2GFOh8xpUV7eT4AQqhA&hl=en&sa=X&ei=7FFvUcK7KcjmrAfMq4DIBQ&ved=0CE0Q6AEwBA#v=onepage&q=which%20dml%20generates%20more%20undo&f=false] this one.
                          but I suspect you must be overlooking some caveats. It can't be that simplistic.
                          agreed, but at the same time it should not be that simplistic when it is asked. If I asks you what you like most to eat from Biscuits, Chocolate, Burger, then probably you will choose one of them, but obviously it is not really that simplistic. May be you like to eat more Biscuits in summer than winter or vice - versa. So my question should be what you like to most to eat in summer/winter from <options>. Life and IT both are same i.e. each and everything, every time, every where "IT DEPENDS".
                          does the DELETE still generate the most undo?
                          With due respect, obviously, when we are dealing with more rows, more undo will be generated, but again in the proportionate only , it is well known and well documented too; more over I am really surprised to see above example in this context from your side. How and why you and Mark are advocating for number of rows, while it seems OP is asking for a single row (question style) and if still OP seems to ask something different and my reply is not correct or wrong, I am really sorry 100 times.

                          Regards
                          Girish Sharma
                          • 10. Re: Which DML generates the most UNDO ?
                            EdStevens
                            rp0428 wrote:
                            >
                            RP, I agree with Ed. The response should have included some qualifying statement like "when dealing with the same row of data" to be more specific to ensure the OP understands the limitations of the answer.
                            >
                            Girish's response did have 'some qualifying statement. He provide the reference to the expert resource that he used.

                            Everything is subject to interpretation. Sometimes you just need to use common sense. I don't consider it common sense to interpret OPs question to be asking about possibilities where one of those ops is for ONE row and one is for 100 million rows. That is just ridiculous on the face of it, IMHO. :D
                            The ridiculous is intentional, to make the point painfully obvious. Once the point is made, we can negotiate the actual boundary conditions.

                            A man and a woman are on an elevator. The man asks the woman if she will perform certain 'services' for a million dollars. She thought a minute and said, "Sure. Why not?". Then the man said "How about for ten dollars?" She said "what kind of woman do you think I am?" He replied, "We've already established that. Now we're just negotiating the price."

                            Now, I wonder if the forum filter will let me actually post that ...

                            Let's assume this is a test question:
                            >
                            Which DML generates the most UNDO ?

                            A. INSERT
                            B. DELETE
                            C. UPDATE
                            >
                            Remember - this is a TEST. On tests you need to select 'the best, or most correct' answer.

                            I would choose 'B. DELETE' since I consider that to be the 'most correct' given the information available.

                            Why don't you and Ed tell us 'which of those three possible answers' you would choose?
                            If it were a written, multi-choice test, I would concur. If it were an interview (the OP really hasn't said) or just a discussion among colleagues, I wouldn't allow myself to be constrained by multiple choice. I don't know what actually prompted the OP's question, but we'd all have to agree we've seen plenty of people come here either preparing for or doing a post-mortem on an interview and it is painfully obvious they treat an interview like it were an exam, seeming to be oblivious to the idea they might actually discuss a question and answer with the interviewer.

                            But I digress ..
                            :-)
                            • 11. Re: Which DML generates the most UNDO ?
                              EdStevens
                              >
                              ... if still OP seems to ask something different and my reply is not correct or wrong, I am really sorry 100 times.
                              >
                              Regards
                              Girish Sharma
                              No need to apologize. The back-and-forth is educational for all. I certainly don't take any of it personally and I trust that neither do the other participants. The OP probably got much more than he expected, but if he has read through it all, he also has a far better understanding than if the only response he had gotten was simply "answer 'B'". He definitely got the "why" part of his question.
                              • 12. Re: Which DML generates the most UNDO ?
                                rp0428
                                Now it is YOU who digress.

                                We are still waiting to hear the final result from the elevator! :D