dcsimg
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13
  1. #1
    Join Date
    Mar 2003
    Location
    Washington, D.C.
    Posts
    3

    Mass update of heavily indexed fields

    Hello everyone, hope someone out here can help me. I am fairly new to Oracle and have just a little SQL experience, nothing extensive. I just started working at a new company and they asked me to 'take a look' at this and see what I could do.

    We have about 80 tables that contain SSN as a key field. Some of these tables are fairly large (1 million+ records), the rest vary from a few thousand to a few hundred thousand. We need to convert the ssn values as we are no longer allowed to use SSN, but now need to use employee id. I have been trying to use a SQL statement to perform the update, using a temporary table (SSN_XREFERENCE) that contains the old ssn (old_ssn) number, the new employee id (new_ssn), and a 'conversion segment' (conv_segment) value that I use to limit the total number of records converted in one transaction. Basically, my SQL looks like this:

    update table_name
    set ssn = (
    select new_ssn
    from SSN_XREFERENCE
    where old_ssn = ssn)
    where EXISTS (
    select old_ssn
    from SSN_XREFERENCE ';
    where old_ssn = ssn
    and CONV_SEGMENT = '1')

    I keep getting RBS errors, also it can take quite a long time to run through all the records (10+ hours), every record in every table will have at least one change. On some of the tables, there are quite a lot of indexes (5-10) that include the ssn field.

    Is this performance to be expected? Do I just need to make the conversion segments smaller to avoid the RBS Errors? Do the large number of indexes have anything to do with the update speed? can I turn off the index updates until after all the changes are done and then re-index the table as a whole. Is there anything else you can think of that I am missing?

    Thank you for any suggestions you may have, if you need any more info, I will be happy to provide anything I can.

    Thanks!
    Charlie

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: Mass update of heavily indexed fields

    Originally posted by LordPhilomont
    I keep getting RBS errors
    What are your error messages?
    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
    Mar 2003
    Location
    Washington, D.C.
    Posts
    3
    Here is the error:

    -1562: ORA-01562: failed to extend rollback segment number 2
    ORA-01650: unable to extend rollback segment R01 by 128 in tablespace RBS

    Thanks for the quick response!
    Charlie
    Last edited by LordPhilomont; 03-18-2003 at 05:32 PM.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Yes, do smaller transactions.
    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
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Use cursor....
    and commit Tran regularly...may be every 20000 records.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Apr 2002
    Location
    Bangalore
    Posts
    47

    Thumbs up

    Try increasing the max extents for the Rbs


    Cheers
    Murali

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by dasari98
    Try increasing the max extents for the Rbs


    Cheers
    Murali
    well the error is not saying maxextents reached, it says it cannot extend, i.e the tablespace's full so no matter how big you set RBS maxextents you will get the same error

  8. #8
    Join Date
    Apr 2002
    Location
    Bangalore
    Posts
    47

    Smile

    Originally posted by pando
    well the error is not saying maxextents reached, it says it cannot extend, i.e the tablespace's full
    Well then adding a datafile or increasing the size of the existing files would do.

    Cheers
    Murali

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by dasari98
    Well then adding a datafile or increasing the size of the existing files would do.

    Cheers
    Murali
    for such kinda errors adding data file that too for rollback segs TBS is not wise....

    rather u could handle in program....so y go 4 new datafile?

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Apr 2002
    Location
    Bangalore
    Posts
    47

    Smile

    Originally posted by abhaysk
    for such kinda errors adding data file that too for rollback segs TBS is not wise....

    rather u could handle in program....so y go 4 new datafile?

    Abhay.
    Hey abhay.i completly agree with u. Handling in the program is perfect.The option i gave is if u can't do that

    Cheers
    Murali

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




×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.