[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Oracle Stored Procedure

Posted on 2011-09-16
11
Medium Priority
?
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 143

Expert Comment

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

Expert Comment

by:HainKurt
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 60

Expert Comment

by:HainKurt
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 60

Expert Comment

by:HainKurt
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 60

Expert Comment

by:HainKurt
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 1500 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

650 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