Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Need to delete rows fast

Uday_NDec 27 2021

Hi All,
I need to delete a million records fastly . I have tried a few options but all taking time . I have provided the example
Table :
CREATE TABLE SOURCE_TABLE ( ID NUMBER, CREATE_DATE DATE) ;
CREATE TABLE TARGET_TABLE ( TG_ID NUMBER , ID NUMBER , CREATE_DATE DATE);
My scenario:
I need to delete target_table records based on the id and today's date ;
Volume of data in target table to be deleted is 108444 . It takes a lot of time more than 2-3 hours . Some time at backend data has been deleted but session out is coming . Total records in the target table is 228997
Query I tried :
delete from target_table where trunc(create_date ) = trunc(sysdate) and id in (select id from source_table) ;
Query 2 I tried :
delete from target_table where trunc(create_date ) = trunc(sysdate) and id in (select id from source_table) ;
Query 3 I tried:
set serveroutput on
declare
cursor c1 is
select * from source_table;
begin
for i in c1 loop
delete from target_table where trunc(create_date) = trunc(sysdate) and id = i.id
end loop ;
end ;

In 1st and second query it is taking more than 2 hours time while in 3rd query it took more than 5 hours and session was timed out but deleted at the back end .

Is there any way to quickly delete the records ? I didnt try forall delete . should i try that as well ? Please kind your advice ?

Regards,
Uday

This post has been answered by Uday_N on Dec 27 2021
Jump to Answer

Comments

Processing

Post Details

Added on Dec 27 2021
18 comments
967 views