Help regarding the UPDATE statement
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Help regarding the UPDATE statement

  1. #1
    Join Date
    Jun 2010
    Posts
    4

    Help regarding the UPDATE statement

    I have two tables. say EMP and EMPINFO

    Both the tables contains two columns say empid and empno

    The EMPNO coulmn is null in the table EMP.

    Now i want to update EMP table and set the values of EMPNO based on the EMPID in the tables EMP and EMPINFO.

    Can we use rowid in this??or any other optimazation query to do this??

    Please help.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Assuming both emp.empid and empinfo.empid allow no duplicates... something like this?
    Code:
    update  EMP
    set     EMP.EMPNO = (select EMPINFO.EMPNO
                         from   EMPINFO
                         where  EMP.EMPID = EMPINFO.EMPID)
    In the affirmative case be sure there is an index on empinfo.empid
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jun 2010
    Posts
    4
    Thanks.

    I used following query

    update /*+ parallel (e,8) */ emp e
    set e.e_emp_id = (select /*+ parallel(ei,8) */ ei.ei_emp_id from empinfo ei
    where e.e_emp_no = ei.ei_emp_no)
    WHERE EXISTS (SELECT 1
    FROM /*+parallel(ei,8) */ empinfo ei
    WHERE e.e_emp_no = ei.ei_emp_no)

    BUt it is taking a lot of time. The record count in both the table is around is 13 million.

    Can anybody,please help me in optimising this query please?

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Be sure there is an index on empinfo.ei_emp_no and take out parallel hint from inline views.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Jun 2010
    Posts
    4

    Unhappy

    Thanks PAVB...

    But that didn't worked..

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Please trace it. Post execution plan and SQL area stats.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Jun 2010
    Posts
    4
    Thanks...I am trying to use cursors now..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width