• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Oracle Stored Procedure

I have a stored procedure that I am trying to delete records from a table using a loop condition but it does not seem to be working. Can anyone help me on this. Thank you in advance.

_____________________________________________________________________________

CREATE OR REPLACE PROCEDURE ATGADMIN."MARKERS_CLEANUP" as

 

CURSOR db_markers

is

  select marker_id from atgadmin.dps_markers;


 

 

v_count   number := 0;

 

 

BEGIN

 

for i in db_markers

loop

 

  delete from atgadmin.dps_markers

  where i.marker_id not in (select marker_id from atgadmin.dps_usr_markers);

  v_count := v_count + 1;

 

if v_count = 20000

then

  commit;

v_count := 0;

end if;

end loop;

commit;

EXCEPTION

    WHEN others THEN

    commit;

END;

/

0
Rdichpally
Asked:
Rdichpally
  • 4
  • 4
  • 2
  • +1
1 Solution
 
RdichpallyAuthor Commented:
"DPS_USR_MARKERS"    2019822 rows

"DPS_MARKERS"    6887517 rows

 

  select count(marker_id) from dps_markers

  where marker_id not in (select marker_id from dps_usr_markers);

 

4890223 rows
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you you please clarify what exactly should be the result?

if you want to delete rows from 1 table where there is no record in the other table, just do a

DELETE sometable  t
 WHERE NOT EXISTS ( SELECT NULL FROM othertable o WHERE o.linked_field = t.linked_field )
; 

Open in new window


no need for a loop or cursor, which make this overall less performant ...
0
 
RdichpallyAuthor Commented:
do you mean the query shoule be :

Delete from atgadmin.dps_markers t
where Not Exists (select null from atgadmin.dps_usr_markers o where o.marker_id = t.marker_id);

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
exactly
0
 
HainKurtSr. System AnalystCommented:
or maybe this

delete from atgadmin.dps_markers
where marker_id not in (select marker_id from atgadmin.dps_usr_markers);
0
 
HainKurtSr. System AnalystCommented:
and again, delete all codes in your sp :)
CREATE OR REPLACE PROCEDURE ATGADMIN."MARKERS_CLEANUP" as
BEGIN
  delete from atgadmin.dps_markers 
   where marker_id not in (select marker_id from atgadmin.dps_usr_markers);
END;
/

Open in new window

0
 
HainKurtSr. System AnalystCommented:
and no need for commit...
0
 
RdichpallyAuthor Commented:
This will not work as we need to commit every 20000 rows as the table is very large (4.1 million rows) and we need to delete rows in batches and commit. Please help ASAP. Thanks.
0
 
HainKurtSr. System AnalystCommented:
in a loop, you can delete 20000 and commit, until all deleted... something like this

loop
  delete from atgadmin.dps_markers
   where marker_id not in (select marker_id from atgadmin.dps_usr_markers)
  and rownum<=2000;
  commit;
select count(1) into v_remaining from atgadmin.dps_markers
   where marker_id not in (select marker_id from atgadmin.dps_usr_markers);
 EXIT WHEN v_remaining = 0;
end loop;

0
 
Devinder Singh VirdiCommented:
>> as the table is very large (4.1 million rows)
You can think in other way, which is insert of required rows in other table and swap the names/indexes/constraints etc.
Delete is very expensive and will take lots of resources than Insert.
If you still prefer to use delete, then you can think of
1.  using bind variables
2.  create say 20 small tables (pctfree 0) with rowid (sorting helps in performance) of main table, and run 20 diff session to speedup.
0
 
RdichpallyAuthor Commented:
Thanks.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now