Solved

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

Posted on 2016-08-31
18
38 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
Comment Utility
How do a update in a cursor? rather than fetching each time
0
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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 31

Accepted Solution

by:
awking00 earned 250 total points
Comment Utility
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
 
LVL 6

Author Comment

by:anumoses
Comment Utility
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
Comment Utility
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
Comment Utility
My only question and worry is the for 5 million records how much time will it take?
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
That doesn't surprise me but, if you only need to do the update once, maybe 5 hours isn't too bad.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Author Comment

by:anumoses
Comment Utility
I added

where woi.action_on_close is null

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

Expert Comment

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

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
v_sql := 'alter index '||i.index_name||' rebuild online parallel 63 nologging';

Can I add this?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now