Scenario Question 2
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Scenario Question 2

  1. #1
    Join Date
    Jun 2000
    Posts
    55
    Suppose your DB is OLTP and you have 50 users online. Your DB is in archive log mode and has enough space in the archive directory. You have a table named Table_A. It has 1 million rows.

    You execute this:

    delete from Table_A;

    Can anyone tell me what exactly will happen next to the database? Exactly what will be written to online redo logs?


    Now you modify your SQL and make it commit every 5000 rows. Will you have the same amount of redo?

    How about commit every single row? Will that be good to the DB?

    Qingbo

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    The pre- image of each and every row will be recorded in the online redo-logs. This will happen regardless if you commit once, once per row, or every 5000 rows. The same amout of redo will be generated.

    Your rollback segments, however, are a different story. With one commit, you will need one large rollback segment. Committing every row you will still need a large rollback segment because Oracle needs a consistent view of the data from the start of the statement until the end. Otherwise, you will get a "snapshot too old" message.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jun 2000
    Posts
    55
    Committing every row you will still need a large rollback segment?
    Wouldn't be true that the blocks in RBS be freed after each commit?

    Every commit will signal a completion of the current transaction, correct?


  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I assumed you were opening a cursor on the table and deleting a row at a time by the PK. If you are submitting a delete statement for each row, then no, your rollback segments would not get very big.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Posts
    344
    I think committing every row is a bit overkill. It would be faster to delete every 1000 or so.

    You could do it like this :

    declare
    procedure del_1000 is
    begin
    delete from mytable where rownum < 1000;
    commit;
    end;
    Begin

    del_1000;
    while SQL%rowcount > 0 loop
    del_1000;
    end loop;

    end;


  6. #6
    Join Date
    Nov 2000
    Posts
    344
    of course the fastest way is :

    TRUNCATE MYTABLE;


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width