Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78

    Unanswered: Slow update questions

    I use Oracle 9 with TOAD Professional.
    I have a table with 3.8 million rows.
    It has a indexed VARCHAR2 field of 12 that i was trying to update with:
    UPDATE CAIF SET MYCHAR = '1234567'

    When I run it, well, it took at least 4 hours. I had to stop it, so I cancelled the query in TOAD. It took another 25 minutes before I terminated TOAD via Task Manager. I then gave it 12 hours, went back in and rebooted.

    My questions are:
    - why would an update be so slow?
    - is there a way to defrag or "straighten up" a database so that in case there is corruption, it would be cleaned up?


    Jerry

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you were updating 3.8 million rows of data.
    you are doing a full table scan and not using the index (you are UPDATING the index).
    think about what oracle has to do.
    1. mark what rows and their values are in the table when you issue the statement
    2. as the rows get updated, also update any indexes using that column
    3. as the rows get updates oracle needs to create rollback space (3.8 million rows) in case you cancel your statement or do not commit
    4. keep track of any/all rows that got inserted or updated while YOUR update was running
    5. also check any/all constraint relationships as the update occurs
    6. if you cancel your statement (rollback) then oracle has to UNDO everything it had updated up to that point
    (there's probably 7&8 but you get the point)

    to actually USE your index you would issue something like:
    UPDATE CAIF
    SET MYCHAR = '1234567'
    where INDEXED_COLUMN = 'X';


    update
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jun 2005
    Location
    Waukesha WI
    Posts
    78
    so theoretically by adding an index, I'm also creating more work and lengthening the time.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by jer99
    so theoretically by adding an index, I'm also creating more work and lengthening the time.
    yes. especially with the update you are using.
    of course if you are updating a column that is not indexed then no additional overhead.

    If you have a large update with a WHERE clause then you would want the
    columns listed in the WHERE clause indexed (most likely, but testing never hurts)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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