This discussion is archived
1 2 Previous Next 23 Replies Latest reply: May 14, 2013 9:24 PM by 894936 Go to original post RSS
  • 15. Re: Update tunining
    jeneesh Guru
    Currently Being Moderated
    The below code will update the whole table because - You are applying all the filters on the table account_t_backn18.
    And, it will update all a.acct_stop_COde as NULL whereever your filter conditions in the corelatd subquery is not matching.
    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'
      )
    Ideally your query should be like below. This will update only the specific records in account_t.If this qury is taking time post the details as mentioned in FAQ:{message:id=9360003}

    You can think of an INDEX on ACCT_STOP_CODE(COMP_CODE ,acct_no) if it is not there..
    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('201211180630','YYYYMMDDHH24MI')
  • 16. Re: Update tunining
    Manik Expert
    Currently Being Moderated
    891933 wrote:
    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
    First check if your previously initiated session was killed or not.
    you may monitor sessions in V$session
    SELECT *
      FROM v$session
     WHERE status = 'ACTIVE' AND username IS NOT NULL;
    Cheers,
    Manik.
  • 17. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    can we use any plsql dynamic script or bulk update...
  • 18. Re: Update tunining
    Manik Expert
    Currently Being Moderated
    Did you try/check Jeneesh's solution above? post whether that worked or not..

    Reg your question, SQL is always better than PL/SQL.

    You may also want to check parallel hint for your task.

    Cheers,
    Manik.
  • 19. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    i have created uniq index on both the tables...
    will it help to tune the query
  • 20. Re: Update tunining
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    891933 wrote:
    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') &lt; '201211180630'
    Read the comments so far: your statement as shown will update EVERY row in account_t, setting the acct_stop_code to NULL if the row doesn't match the last three conditions in your statement - is this really what you want ? You've already been given a modified statement that will only update the rows that match the last three predicates.

    Once you've figured out what you're really trying to achieve, and the most efficient way to achieve it, then it's possible that an index on account_t_backn18(comp_code, acct_no), possibly with the columns in the opposite order, will help. But we can't tell you that based on the information we've received so far.

    Regards
    Jonathan Lewis
  • 21. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    Hi Jonathan Lewis

    Thank you... for making me to understand the query...
    I want to update the acct_stop_code of account_t to account_t_backn18 acct_stop_code

    that is i want to replace the stop_code of account_t table with the stop_code of acct_t_backn18 table values.

    here if it returns null means then my requirement does not match.

    but the remaining conditions shd be matched ...

    I tried to implement with Bulk collect...but not getting proper idea
    Bulk collect
  • 22. Re: Update tunining
    William Robertson Oracle ACE
    Currently Being Moderated
    I think the condition
    and to_char(a.upd_date,'YYYYMMDDHH24MI') &lt; '201211180630'
    is logically equivalent to
    and a.upd_date &lt; to_date('2012-11-18 06:31','YYYY-MM-DD HH24:MI:SS')
    because the TO_CHAR rounds the time down to the nearest minute, so without rounding (and assuming UPD_DATE is a DATE column)you are actually looking for any time before 06:31 (not 06:30).
    891933 wrote:
    i have created uniq index on both the tables...
    will it help to tune the query
    It depends what columns the index is on.

    btw please use
     tags just for code. It makes the thread hard to read if you use them for ordinary text. Thanks...
    
    Edited by: William Robertson on Nov 21, 2012 6:27 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
  • 23. Re: Update tunining
    894936 Newbie
    Currently Being Moderated
    Thanks alot for your help
1 2 Previous Next

Legend

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