This discussion is archived
8 Replies Latest reply: Feb 21, 2013 6:20 AM by Paul Horth RSS

Tuning Update query

SC_CS Newbie
Currently Being Moderated
Hi,

I have a query updating a table having millon of rows.
update table_name set id=5
This query is taking 35 minutes to execute every table my table is run for new data.

Is there any way to tune this query.

Thanks
  • 1. Re: Tuning Update query
    Chanchal Wankhade Journeyer
    Currently Being Moderated
    Hi,

    Create index on id column. It may take some litle time. But do remember you have milions of records so it will take some time. Be patiance.

    Index syntax:-
    create index emp_ind on emp(empcode);
  • 2. Re: Tuning Update query
    Suman Rana Explorer
    Currently Being Moderated
    Try it... may work...


    update table_name set id=5
    where LNNVL(id=5);
  • 3. Re: Tuning Update query
    user503635 Explorer
    Currently Being Moderated
    Would suggest
    - use Parallel hint, parallel degree will suggest your count of CPU * 2
    - disable any foreign key constraints on ID field if any
    - if most ID is already 5, then add WHERE clause to exclude them
     
    
    update /*+ PARALLEL (t, 8) */ table_name t
       set ID = 5
    /
    or
    update /*+ PARALLEL (t, 8) */ table_name t
       set ID = 5
     where ID != 5 or ID is NULL
    /
    Edited by: user503635 on Feb 21, 2013 5:13 AM

    Edited by: user503635 on Feb 21, 2013 5:14 AM

    Edited by: user503635 on Feb 21, 2013 5:14 AM
  • 4. Re: Tuning Update query
    Paul Horth Expert
    Currently Being Moderated
    Chanchal Wankhade wrote:
    Hi,

    Create index on id column. It may take some litle time. But do remember you have milions of records so it will take some time. Be patiance.

    Index syntax:-
    create index emp_ind on emp(empcode);
    Really, how will putting an index on the id column help when he is setting every row to have an id of 5?

    That would slow the update down.
  • 5. Re: Tuning Update query
    Paul Horth Expert
    Currently Being Moderated
    shavetachawla wrote:
    Hi,

    I have a query updating a table having millon of rows.
    update table_name set id=5
    This query is taking 35 minutes to execute every table my table is run for new data.

    Is there any way to tune this query.

    Thanks
    Do you really want to set all the million rows to have an id of 5?
  • 6. Re: Tuning Update query
    SC_CS Newbie
    Currently Being Moderated
    Yes Paul this is the requirement to set id=5 for all rows
  • 7. Re: Tuning Update query
    riedelme Expert
    Currently Being Moderated
    shavetachawla wrote:
    Hi,

    I have a query updating a table having millon of rows.
    update table_name set id=5
    This query is taking 35 minutes to execute every table my table is run for new data.

    Is there any way to tune this query.

    Thanks
    An index will probably not help this UPDATE because you are not using a WHERE clause to restrict rows.

    The parallel query option might help - if you have the license. Also possible is parallel DML which is enabled apart from running the background select in parallel. You can read about parallel DML in the documentation.

    Is this a one-time event? If so it might be easiest just to wait for it to finish.
  • 8. Re: Tuning Update query
    Paul Horth Expert
    Currently Being Moderated
    shavetachawla wrote:
    Yes Paul this is the requirement to set id=5 for all rows
    That's quite an unusual requirement.

    You could look into using parallelism, as others have suggested.

    If it is always going to be 5, another way is to cheat: define a view on the base table that always returns 5 for the id.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points