-
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.
-
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.
-
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?
-
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.
-
Thanks PAVB...
But that didn't worked..
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|