8 Replies Latest reply: Feb 21, 2013 8:20 AM by Paul Horth RSS

    Tuning Update query

    SC_CS
      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
          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
            Try it... may work...


            update table_name set id=5
            where LNNVL(id=5);
            • 3. Re: Tuning Update query
              user503635
              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
                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
                  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
                    Yes Paul this is the requirement to set id=5 for all rows
                    • 7. Re: Tuning Update query
                      riedelme
                      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
                        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.