Solved

Oracle Stored Procedure

Posted on 2011-09-16
11
370 Views
Last Modified: 2012-05-12
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
Comment
Question by:Rdichpally
  • 4
  • 4
  • 2
  • +1
11 Comments
 

Author Comment

by:Rdichpally
ID: 36549608
"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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36549644
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
 

Author Comment

by:Rdichpally
ID: 36549693
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
Independent Software Vendors: 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!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36549733
exactly
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36549808
or maybe this

delete from atgadmin.dps_markers
where marker_id not in (select marker_id from atgadmin.dps_usr_markers);
0
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36549815
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36549821
and no need for commit...
0
 

Author Comment

by:Rdichpally
ID: 36550037
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
 
LVL 51

Expert Comment

by:Huseyin KAHRAMAN
ID: 36550380
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
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 500 total points
ID: 36550563
>> 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
 

Author Closing Comment

by:Rdichpally
ID: 36711967
Thanks.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
1 FROM DUAL wont work with additional columns ?? 4 55
oracle DR - data guard failover. 18 57
pivot rows to columns 1 44
DECLARATION OF CURSOR IS INCOMPLETE OR MALFORMED 5 40
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

713 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