asked on

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

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.

anumoses

ASKER

How do a update in a cursor? rather than fetching each time

anumoses

ASKER

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

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

Getting error on this line Founbd woi when expection .....

ORA-00969: missing ON keyword

on woi.wo_id = wo.id

anumoses

ASKER

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

anumoses

ASKER

My only question and worry is the for 5 million records how much time will it take?

awking00

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?

anumoses

ASKER

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

awking00

That doesn't surprise me but, if you only need to do the update once, maybe 5 hours isn't too bad.

anumoses

ASKER

I added

where woi.action_on_close is null

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

awking00

Glad to hear it. That filter would definitely improve performance if only a relatively few null values needed to be updated.

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anumoses

ASKER

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.

anumoses

ASKER

thanks

Mark Geerlings

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)

anumoses

ASKER

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

awking00

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;
/

anumoses

ASKER

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

Can I add this?