This discussion is archived
1 2 Previous Next 23 Replies Latest reply: May 14, 2013 9:24 PM by 894936 RSS

Update tunining

894936 Newbie
Currently Being Moderated
Hi Team,
could you please help me out... as i am running the below update statement in production.
Whole night i was running this script... suddenly my remote system has been restarted... my script dint run i think so.

I f i am going to run the script again it takes some much time to update... as their are 4 lakhs of records to be updated.
Can u please in tuning this query.... so that i can run my script within short time...
update account_t a 
set a.acct_stop_COde=(select B.ACCT_STOP_CODE
from account_t_backn18 b
where a.comp_code=B.COMP_CODE
and a.acct_no=b.acct_no
and a.comp_code <> '1206' 
and a.acct_stop_code = '01'
and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '201211180630'
Edited by: 891933 on Nov 19, 2012 9:24 PM
  • 1. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '201211180630') 
    What is this? Is this code complete?

    Why are you converting a DATE in to Character, before comaring using less than?
  • 2. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    and should it not be like this?
    update account_t a 
    set a.acct_stop_COde=
         (     select B.ACCT_STOP_CODE
         from account_t_backn18 b
         where B.COMP_CODE=a.comp_code
         and b.acct_no=a.acct_no
         )
    and a.comp_code  '1206' 
    and a.acct_stop_code = '01'
    and a.UPD_DATE < to_date(......
    And have a look at the FAQ: {message:id=9360003}
  • 3. Re: Update tunining
    Purvesh K Guru
    Currently Being Moderated
    Please consider posting the details as mentioned in {message:id=3292438}.

    I f i am going to run the script again it takes some much time to update... as their are 4 lakhs of records to be updated.
    Also, this being an International Forum, usage of Locale is not encouraged. You can mention 4 Lakhs as 400K records; And with Oracle 400K isn't a big number to process.
    Hence, it will help us if you can post the details mentioned in the linked Thread.
  • 4. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    Yes my query is complete...
    any idea how do we update the below script by using rowid?
    update account_t a 
    set a.acct_stop_COde=(select B.ACCT_STOP_CODE
    from account_t_backn18 b
    where a.comp_code=B.COMP_CODE
    and a.acct_no=b.acct_no
    and a.comp_code <> '1206' 
    and a.acct_stop_code = '01'
    and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '201211180630');
  • 5. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    891933 wrote:
    Yes my query is complete...
    What is the meaning of the last line of your update statement?
  • 6. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    last line  iam selecting the the records of the upd_date before nov18th
    
    2012 month 11 date 18th timing 6.30 am
    
    and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '201211180630') ;
    Edited by: 891933 on Nov 19, 2012 9:34 PM
  • 7. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    Please read the FAQ, as mentioned in the previous posts..
  • 8. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    can you send me any sample script for updating using rowid for my scripts...
  • 9. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    Your UPDATE script is WRONG....

    The last line you posed is incomplete..

    And do you know - That UPDATE script is going to update the WHOLE TBALE, without any filters (If you correct the last line). This is because all your filters are in the SUBQUERY..

    probably you are going to loose your job...
  • 10. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    update account_t a
    set a.acct_stop_COde=(select B.ACCT_STOP_CODE
    from account_t_backn18 b
    where a.comp_code=B.COMP_CODE
    and a.acct_no=b.acct_no
    and a.comp_code <> '1206'
    and a.acct_stop_code = '01'
    and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '201211180630')
    Hi Thanks for information...
    my query is running fine...if my query was worng then how can i say that its taking 4  hours to update....
     its taking too long time...
    when i post third line its not getting desplyed in forum...
  • 11. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    Are you trying to update the whole table?

    or only the rows with the the below filters?
    and a.acct_no=b.acct_no
    and a.comp_code '1206' 
    and a.acct_stop_code = '01'
    and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') < '
  • 12. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    Hope this is your complete qury..
    update account_t a 
    set a.acct_stop_COde=
      (     select B.ACCT_STOP_CODE
         from account_t_backn18 b
         where a.comp_code=B.COMP_CODE
         and a.acct_no=b.acct_no
         and a.comp_code '1206' 
         and a.acct_stop_code = '01'
         and to_char (a.UPD_DATE, 'YYYYMMDDHH24MI') <''201211180630'
      )
  • 13. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    Hi yes... that is my complete query...
    could you please tell me how to improve the perfomracne
    
    Thanks
  • 14. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    I need to update based on the the filters mentioned in the script.
    Now i need to do this step directly in production... as many of the accounts has been stopped..
    so it would be great help if you do the needfull Thanks.
1 2 Previous Next

Legend

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