?
Solved

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

Posted on 2016-08-31
18
Medium Priority
?
92 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
  • 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses
Course of the Month15 days, 23 hours left to enroll

850 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