Solved

Oracle Stored Procedure

Posted on 2011-09-16
11
375 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 143

Expert Comment

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

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 53

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 53

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 53

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

734 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