Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    india,hyderabad
    Posts
    43

    Talking Unanswered: re: update statement

    what are the different techniques that one can use
    to decrease the overall time it takes to do

    update of a large table ....... ?????

    thanks for your replies
    harry

  2. #2
    Join Date
    Aug 2003
    Posts
    41
    On large updates, you should have a good eye on your rollback segment.


    You can create a large rollback segment and issue your update statement after the statement:

    set transaction use rollback segment <large rollback segment>

    OR

    You can update the table part by part with the help of your primary key field, like

    where pk between 1 and 100000
    where pk between 100000 and 200000
    and a commit in between so that your rollback segment wont get eaten up.

    OR

    You can write a small PL/SQL to do the above using cursors and a commit in between.

    OR

    there are many other ways.

    -Sunil.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Some things which may help

    1) If your subquery is using a correlated subquery then check to see if it is using an index as this may make a big difference (depending on the number of rows in the associated tables).

    2) Check your table are analyzed.

    3) Updates may cause row chaining so it is a good idea to check your pctfree. It might be worthwhile using alter table move to alter this value.

    4) Bulk binds can also make a big difference if your using plsql to do your update.

    Alan

Posting Permissions

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