This discussion is archived
12 Replies Latest reply: Feb 11, 2010 11:59 AM by Jonathan Lewis RSS

Optimizing an update statement

411002 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    I think by re orgnizing the Index column order should solve your problem. Try it.
  • 7. Re: Optimizing an update statement
    411002 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    For some reason user2224721 has resurrected a five year old thread.
  • 12. Re: Optimizing an update statement
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    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