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