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
Solved

Oracle Stored Procedure

Posted on 2011-09-16
11
364 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 143

Expert Comment

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

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 51

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 51

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 51

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 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

861 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