12 Replies Latest reply: Feb 11, 2010 1:59 PM by Jonathan Lewis RSS

    Optimizing an update statement

    411002
      I have the following update statement that I want to run everyday to perform a logical lock on records that are older than so many days. Here is my query

      Update treatment
      set locked_flag = 'Y'
      where treatment_date <= sysdate -90
      and locked_flag = 'N';

      There is an index on the treatment_date and locked_flag columns. Using the <= on the date causes this index to not be used. This table has 3+ million records, and I am updating about 1,500 a day. I would really like to prevent this update statement from doing a full table scan.

      Thanks.
        • 1. Re: Optimizing an update statement
          APC
          If you are running this every day then all of the records older than 90 days ago will have had the flag set so you're not interested in them but[i] the optimizer doesn't know this. Try rewrtiting your query to look something like this...
          Update treatment
          set locked_flag = 'Y'
          where treatment_date BETWEEN sysdate -92 AND sysdate -90
          and locked_flag = 'N';
          It just might work...

          Cheers, APC
          • 2. Re: Optimizing an update statement
            Nicolas.Gasparotto
            Hi,

            You can study the possibility to create a bitmap index on locked_flag column.
            It can improve performance...
            Have you calculate statistics ?

            Nicolas.

            Hum, perhaps not a good idea since bitmap don't like update...
            Message was edited by:
            N. Gasparotto
            • 3. Re: Optimizing an update statement
              411002
              Trouble is the users enter the treatment_date field, so they could enter a date of yesterday, or a week ago.

              The index will be utilized if I run the query like you proposed:
              Update treatment
              set locked_flag = 'Y'
              where treatment_date BETWEEN sysdate -92 AND sysdate -90
              and locked_flag = 'N';

              But if I run this query it doesn't use the index:
              Update treatment
              set locked_flag = 'Y'
              where treatment_date BETWEEN to_date('01-JAN-1900') AND sysdate -90
              and locked_flag = 'N';

              Statistics are updated on this table BTW.
              • 4. Re: Optimizing an update statement
                28044
                Due to update operation is taking on locked_flag, it is not adviceable to use bitmap index.

                try these options. test and get convinced by tracing and then move to production.

                1. Create index on (locked_flag, treatment_date) instead of (treatment_date, locked_flag). Before trying this option, make an impact analysis, that this change does not impacting other quries.

                2. Change where condition to
                treatment_date between to_date('01/01/1900') and (sysdate - 90)

                3. If stats are collected on this table and Index, force index usage by providing an HINT.

                let us know, how these goes. In the mean time, what is the time it is taking for your current operation and what is expectation.
                • 5. Re: Optimizing an update statement
                  411002
                  This query takes about 15 minutes to execute, and while it is executing it is taking significant db server resources (CPU). This is noticable to the users logged on (the app is a 24x7).

                  The hint doesn't seem to matter, the optimizer is still doing a full table scan.
                  • 6. Re: Optimizing an update statement
                    28044
                    I think by re orgnizing the Index column order should solve your problem. Try it.
                    • 7. Re: Optimizing an update statement
                      411002
                      Doesn't matter the order for the index (I tried both ways), and the order in the where statement doesn't matter either.

                      BTW I am using 10gR2.
                      • 8. Re: Optimizing an update statement
                        28044
                        Surprise, it has to use the index partially atleast for checking the locked_flag.

                        Is the table and index are analyzed?

                        Can you please run and post the result set of the following query:

                        select OWNER, INDEX_NAME, DISTINCT_KEYS
                        from dba_indexes where index_name = <Name of the Index>


                        Okay, in the mean time try this:

                        Update treatment
                        set locked_flag = 'Y'
                        where treatment_date <= sysdate -90
                        and rowid in (select /*+ try both by with or with no Index hint here */
                        rowid from treatment where locked_flag = 'N');
                        • 9. Re: Optimizing an update statement
                          753156
                          If you aren't doing this already, try gathering statistics with the method_opt option - and create histogram on the indexed columns. WIth that oracle will have an accurate estimate of the cardinality of the update and use the index if it thinks that is appropriate.
                          • 10. Re: Optimizing an update statement
                            Jonathan Lewis
                            jspinelli wrote:
                            I have the following update statement that I want to run everyday to perform a logical lock on records that are older than so many days. Here is my query

                            Update treatment
                            set locked_flag = 'Y'
                            where treatment_date <= sysdate -90
                            and locked_flag = 'N';
                            If you can't change the code then the advice to create a histogram on column locked_flag and an index on (locked_flag, treatment_date) could be sufficient to solve the problem,

                            The nicest solution, though is probably to create a function-based index:
                            create index treatment_fbi on treatment(case locked_flag when 'N' then treatment_date else null end);
                            execute dbms_stats.gather_table_stats({table_owner},'treatment',method_opt=>'for all hidden columns size 1')
                            Then change the code to
                            Update treatment set 
                                    locked_flag = 'Y'
                            where    case locked_flag when 'N' then treatment_date else null end <= sysdate - 90
                            If I've got my case statements right, this gives you an index which is as small as it can be (it will hold the treatment_date for just those rows where locked_flag = 'N') and will allow the optimizer to see that you want to update a very small fraction of the data.

                            The only trouble will be if the rows to be updated are very widely scattered through the table - in which case the optimizer might think that even this optimal index isn't worth using.

                            Regards
                            Jonathan Lewis
                            http://jonathanlewis.wordpress.com
                            http://www.jlcomp.demon.co.uk

                            To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                            {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                            fixed format
                            .
                            
                            
                            "For every expert there is an equal and opposite expert"
                            Arthur C. Clarke                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                            • 11. Re: Optimizing an update statement
                              6363
                              For some reason user2224721 has resurrected a five year old thread.
                              • 12. Re: Optimizing an update statement
                                Jonathan Lewis
                                Pointless wrote:
                                For some reason user2224721 has resurrected a five year old thread.
                                And the OP hasn't been on the forum for 4 years!
                                So no points for my briliiant suggestion ;(

                                Regards
                                Jonathan Lewis
                                http://jonathanlewis.wordpress.com
                                http://www.jlcomp.demon.co.uk

                                To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                                {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                                fixed format
                                .
                                
                                
                                "Science is more than a body of knowledge; it is a way of thinking" 
                                Carl Sagan