Bulk update of 302070 records in oracle

Can you not do it in a single update?

Untested but something like:

 update dss.orders
           set active   = 'I'
where ord.sls_chn_id = 'DASI'
and not exists (select 'x'
                 from dss.plan_shipments plshp
                       where plshp.order_id = ord.id
                       )
and not exists (select 'x'
                from dss.ordered_items orditm
                       where orditm.order_id = ord.id
                )
and active='A';

Select allOpen in new window

300 thousand records will take one hour. Very expensive.

OR you may try a parallel update - like

alter session enable parallel dml;

UPDATE  /*+ PARALLEL(ord 8) */ dss.orders ord
SET     active   = 'I'
where ord.sls_chn_id = 'DASI'
and not exists (select 'x'
                 from dss.plan_shipments plshp
                       where plshp.order_id = ord.id
                       )
and not exists (select 'x'
                from dss.ordered_items orditm
                       where orditm.order_id = ord.id
                )
and active='A';

Select allOpen in new window

both are running. What is your thought on the time it may take for 300 thousand records.

I think time is the issue here. Both are taking time. Production will be an issue.

Can you post an execution plan:

explain plan for
 update dss.orders
           set active   = 'I'
where ord.sls_chn_id = 'DASI'
and not exists (select 'x'
                 from dss.plan_shipments plshp
                       where plshp.order_id = ord.id
                       )
and not exists (select 'x'
                from dss.ordered_items orditm
                       where orditm.order_id = ord.id
                )
and active='A';

select * from table(dbms_xplan.display);

Select allOpen in new window

>>both are running

If you are running both at the same time, they are likely competing with each other for resources.  That test isn't valid.

Explain complete.

PLAN_TABLE_OUTPUT                                                              
--------------------------------------------------------------------------------
                                                                               
-----------------------------------------------------------------------------  
| Id  | Operation               | Name              | Rows  | Bytes | Cost  |  
-----------------------------------------------------------------------------  
|   0 | UPDATE STATEMENT        |                   |     1 |   344 | 78640 |  
|   1 |  UPDATE                 | ORDERS            |       |       |       |  
|   2 |   NESTED LOOPS ANTI     |                   |     1 |   344 | 78640 |  
|   3 |    HASH JOIN ANTI       |                   |    10 |  3390 | 78620 |  
|   4 |     TABLE ACCESS FULL   | ORDERS            |   295K|    93M| 54434 |  
|   5 |     INDEX FAST FULL SCAN| ORDITM_ORDER_FK_I |    13M|    74M|  8030 |  
|   6 |    INDEX RANGE SCAN     | PLSHP_ORDER_FK_I  |  4534K|    21M|     2 |  
-----------------------------------------------------------------------------  
                                                                               
Note                                                                            
-----                                                                          
   - 'PLAN_TABLE' is old version                                                

16 rows selected.

Have you tried the PL/SQL block I suggested? How long does it take?

What is the cardinality of orders.sls_chn_id?

If there isn't an index on that column, can you add one and see what it does?

I agree than an index on orders.sls_chn_id may help greatly (if that column is not indexed now, or is not the first column in an index).

Also is the order_id column indexed in the two tables you have "not exists" sub-queries from?

Have you tried "not in" subqueries instead of "not exists" sub-queries for those tables like this:

update dss.orders
           set active   = 'I'
where ord.sls_chn_id = 'DASI'
and ord.order_id not in (select plshp.order_id
                 from dss.plan_shipments plshp)
and ord.order_id not in (select orditm.order_id
                from dss.ordered_items orditm)
and active='A';

Select allOpen in new window

set serveroutput on
declare
type tab_t1 is table of dss.orders.approved_by%type index by binary_integer;
l_ord_ids tab_t1;
l_data tab_t1;

l_cnt number:=0;
l_c number:=0;

l_last         PLS_INTEGER;   
l_start        PLS_INTEGER;   
l_end          PLS_INTEGER; 

cursor c1 is
select rowidtochar(rowid)
from dss.orders ord
where ord.sls_chn_id = 'DASI'
and not exists (select 'x'
                 from dss.plan_shipments plshp
                       where plshp.order_id = ord.id
                       )
and not exists (select 'x'
                from dss.ordered_items orditm
                       where orditm.order_id = ord.id
                )
and active='A'
;
begin
execute immediate('alter trigger orders_pre disable');
open c1;
loop
fetch c1 bulk collect into l_ord_ids limit 500;
exit when l_ord_ids.count=0;    
    for i in 1..l_ord_ids.count
    loop
      l_c:=l_c+1;
      l_data(l_c):=l_ord_ids(i);
    end loop;
    l_cnt:=l_cnt+ l_ord_ids.count;     
  
end loop;
close c1;

dbms_output.put_line('Records Processed='||l_data.count); 

l_start:=1;
l_last:=l_data.count;

loop
  exit when l_start > l_last;
  l_end:=LEAST (l_start + 1000 - 1, l_last);
  begin
                      DBMS_OUTPUT.PUT_LINE ('FORALL start-end: ' || l_start || '-' || l_end);
            forall indx in l_start..l_end
               update dss.orders
                       set active   = 'I'
                     where rowid   =  chartorowid(l_data(indx));
   end;
   commit;
   l_start := l_end + 1;
end loop;
commit;
execute immediate('alter trigger orders_pre enable');
end;
/

Select allOpen in new window

I rewrote the code and disabled the trigger and enabled it. Ran the update for 300,000 records in 5 seconds. Thanks all for the input and help.

We didn't know about a trigger.

Be careful:  The trigger obviously performs a necessary function.  What if someone performs DML that should have fired the trigger while your code is running?  You will have lost that data.

In test database, users will not log ion unless we ask them to test. The data we were updating was to cancel orders that were bad orders. Had only header data but no details or shipment data. So these had to be cancelled (made inactive). Yes that trigger has insert into journal tables, but we were k as it was bad data, caused due to bad state code. No error handling was there so status was still new and those 6 bad orders in the course of time, as the job runs automatically, created 300,000 and odd header data. When we move to production, we bring the system down and its a nightly production job. So we are ok here. Thanks for the concern though. It was a legitimate question asked from your side and I totally agree.

@anumoses - your code is using the ROWID based update as I suggested.

THANKS