Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle- 10.2.04 Update on a table selecting value from another table

Posted on 2016-08-31
18
Medium Priority
?
86 Views
Last Modified: 2016-08-31
I have work orders table and want to update work order items table with a column called action_on_close. Rather than hard coding or defaulting the value I want to take the value from work orders table. Have 5 million records to update. DBA says , Its running out of space. Need help in my query

  -- Run a loop to update 100,000 rows
  DECLARE i NUMBER(10) := 1;
                startd DATE := SYSDATE;
  BEGIN
    WHILE i <= 100000 LOOP
 
   update work_order_items t2
      set t2.action_on_close = (
          select t1.action_on_close
            from work_orders t1
            where t1.id = t2.wo_id
       );
   
      i := i + 1;
 
      -- Commit after each 10,000 row
      IF MOD(i, 10000) = 0 THEN
        COMMIT;
      END IF;
 
    END LOOP;
 
     -- Output the execution time in seconds 
    DBMS_OUTPUT.PUT_LINE((SYSDATE - startd)*24*60*60);
    END;

Open in new window


Is my code doing it for every row?  Help appreciated.
0
Comment
Question by:anumoses
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 5
  • 2
18 Comments
 
LVL 6

Author Comment

by:anumoses
ID: 41777970
How do a update in a cursor? rather than fetching each time
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778083
DECLARE
  commit_interval NUMBER;
  counter NUMBER;
BEGIN
commit_interval := 5000;
counter := 0;
for c1 in(SELECT wo.id,wo.action_on_close
            FROM work_orders wo, work_order_items woi
           where wo.id = woi.wo_id)
LOOP
update work_order_items 
   set action_on_close=c1.action_on_close
 where wo_id=c1.id;
  counter := counter + 1;
  IF counter >= commit_interval THEN
    COMMIT;
    counter := 0;
  END IF;
END LOOP;
COMMIT;
End; 

Open in new window


Made this change. But I am not sure
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 41778119
You might try using the merge function in plain sql -
merge into work_order_items woi
using
(select id, action_on_close
 from work_orders) wo
on woi.wo_id = wo.id
when matched then
update set woi.action_on_close = wo.action_on_close

You can get the elapsed time simply by setting timing on.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 6

Author Comment

by:anumoses
ID: 41778264
Getting error on this line Founbd woi when expection .....

ORA-00969: missing ON keyword

on woi.wo_id = wo.id
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778268
Fixed it

merge into work_order_items woi
using
(select id, action_on_close
 from work_orders) wo
on (woi.wo_id = wo.id)
when matched then
update set woi.action_on_close = wo.action_on_close
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778275
My only question and worry is the for 5 million records how much time will it take?
0
 
LVL 32

Expert Comment

by:awking00
ID: 41778295
Sorry I missed the parentheses for the on clause. Can't say for sure how long it might take. You may want to create small table (100K records) from the 5M record table and see how long it takes to run the merge on that table. Also, is this a one-time update?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778299
yes 1 time update. Its taking lot of time and so far only 1 million records updated and it took 1 hour. I still have 4 million
0
 
LVL 32

Expert Comment

by:awking00
ID: 41778327
That doesn't surprise me but, if you only need to do the update once, maybe 5 hours isn't too bad.
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778368
I added

where woi.action_on_close is null

and the merge happened in 5 minutes. Updated all 4 million records. Thanks.
0
 
LVL 32

Expert Comment

by:awking00
ID: 41778375
Glad to hear it. That filter would definitely improve performance if only a relatively few null values needed to be updated.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 1000 total points
ID: 41778377
If you need to split up this task into multiple sections that can be committed separately, I think a better approach is to avoid the cursor loop on the joined tables, and do a loop on just the master table instead, or if the master table has a numeric key column, avoid the cursor loop entirely, and do an update of the work_order_items table where the woi.wo_id is in the list of wo.id values, and use a subquery of the work_orders table to provide the needed value for each work_order_item table.  Then increment the range of id numbers to work with in the master table and repeat the update, etc, until you reach the end of the master table.

Of course, the obvious question is: why do you want to copy this data from the master table to each corresponding detail table record?  That is data denormalization.  In a DataWarehouse environment, this can help some queries run faster.  But, are you sure that this is the best approach to whatever the business problem is that you are trying to solve?
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778386
The reason is that projects get closed at work_order_item level We are changing the whole routine. When I added the new column to work_order_items table I could not make it NOT NULL and default to C. But now I altered the table.

Requisitions process is changing so all these changes.
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 41778406
thanks
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 41778459
After adding a column to a 5-million row table, and populating the new column, you may now have lots of "chained rows".  These can cause significant performance penalties when querying this data in the future.

I recommend that you check for these, and consider fixing them.  The simplest way to do that is:
alter table work_order_items move tablespace [your_tablespace];

alter index [index_name] rebuild online;  (for each index on the table)
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778614
CREATE INDEX DSS.WOITM_CLONED_FK_I ON DSS.WORK_ORDER_ITEMS
(WOITM_ID_CLONED)

CREATE INDEX DSS.WOITM_FOLDER_FK_I ON DSS.WORK_ORDER_ITEMS
(MV_FOLDER_ID)

CREATE INDEX DSS.WOITM_LOCN_FK_I ON DSS.WORK_ORDER_ITEMS
(LOCN_ID_WHSE)

CREATE INDEX DSS.WOITM_LOCN_STAGED_FK_I ON DSS.WORK_ORDER_ITEMS
(LOCN_ID_STAGED

CREATE UNIQUE INDEX DSS.WOITM_PK ON DSS.WORK_ORDER_ITEMS
(ID)

CREATE UNIQUE INDEX DSS.WOITM_UK ON DSS.WORK_ORDER_ITEMS
(LINE, WO_ID)

CREATE UNIQUE INDEX DSS.WOITM_UK2 ON DSS.WORK_ORDER_ITEMS
(LEVEL_2_CODE, WOLEV1_ID, WO_ID)

CREATE INDEX DSS.WOITM_WOITM_SOURCE_FK_I ON DSS.WORK_ORDER_ITEMS
(WOITM_ID_SOURCE)

CREATE INDEX DSS.WOITM_WOLEV1_FK_I ON DSS.WORK_ORDER_ITEMS
(WOLEV1_ID)

CREATE INDEX DSS.WOITM_WO_FK_I ON DSS.WORK_ORDER_ITEMS
(WO_ID)

These are all the indexes. Do I have to alter all of them?

Thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 41778640
You can create a procedure to manage the indexes.
declare
v_sql varchar2(255);
begin
for i in
(select index_name
 from user_indexes
 where table_name = 'WORK_ORDER_ITEMS'
 and status != 'VALID')
loop
v_sql := 'alter index '||i.index_name||' rebuild online';
execute immediate v_sql;
end loop;
end;
/
0
 
LVL 6

Author Comment

by:anumoses
ID: 41778651
v_sql := 'alter index '||i.index_name||' rebuild online parallel 63 nologging';

Can I add this?
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question