Bulk update of 302070 records in oracle

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

l_cnt number:=0;

cursor c1 is
select ord.id
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
open c1;
loop
fetch c1 bulk collect into l_ord_ids limit 500;
exit when c1%notfound;
forall indx in 1..l_ord_ids.count
   update dss.orders
           set active   = 'I'
         where id   =  l_ord_ids(indx);
l_cnt:=l_cnt+ l_ord_ids.count;     
 dbms_output.put_line('Records Updated='||l_cnt); 
   
end loop;
close c1;

forall indx in 1..l_ord_ids.count
   update dss.orders
           set active   = 'I'
         where id   =  l_ord_ids(indx);

l_cnt:=l_cnt+ l_ord_ids.count;

dbms_output.put_line('Total Records Updated='||l_cnt);
    
         
end;
/

Open in new window


I have 300 thousand odd records to update. Its taking more than 2-3 minutes which makes it very costly or expensive based on time. How can I improve this?
LVL 6
anumosesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
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';

Open in new window

0
anumosesAuthor Commented:
300 thousand records will take one hour. Very expensive.
0
SujithData ArchitectCommented:
Try this -

set serveroutput on
declare
type tab_t1 is table of VARCHAR2(64) index by binary_integer;
l_ord_ids tab_t1;

l_cnt number:=0;

cursor c1 is
select ROWIDTOCHAR(ord.rowid) rid
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
open c1;
loop
fetch c1 bulk collect into l_ord_ids limit 500;
exit when c1%notfound;
forall indx in 1..l_ord_ids.count
   update dss.orders
           set active   = 'I'
         where rowid   =  CHARTOROWID(l_ord_ids(indx));
l_cnt:=l_cnt+ l_ord_ids.count;     
 dbms_output.put_line('Records Updated='||l_cnt); 
   
end loop;
close c1;

forall indx in 1..l_ord_ids.count
   update dss.orders
           set active   = 'I'
         where rowid   =  CHARTOROWID(l_ord_ids(indx));

l_cnt:=l_cnt+ l_ord_ids.count;

dbms_output.put_line('Total Records Updated='||l_cnt);
    
         
end;
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

SujithData ArchitectCommented:
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';

Open in new window

0
anumosesAuthor Commented:
both are running. What is your thought on the time it may take for 300 thousand records.
0
anumosesAuthor Commented:
I think time is the issue here. Both are taking time. Production will be an issue.
0
slightwv (䄆 Netminder) Commented:
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);

Open 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.
0
anumosesAuthor Commented:
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.
0
SujithData ArchitectCommented:
Have you tried the PL/SQL block I suggested? How long does it take?
0
slightwv (䄆 Netminder) Commented:
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?
0
Mark GeerlingsDatabase AdministratorCommented:
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';

Open in new window

0
anumosesAuthor Commented:
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;
/

Open 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.
0
slightwv (䄆 Netminder) Commented:
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.
0
anumosesAuthor Commented:
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.
0
SujithData ArchitectCommented:
@anumoses - your code is using the ROWID based update as I suggested.
0
anumosesAuthor Commented:
THANKS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.