I want to delete all the data from Table1 which is getting inserted between specific timeframe. And that timeframe value I am getting from Table2 columns starttime and finishtime and these two tables have no relations between them. So for doing this, I wrote the below query:
delete from table1 where SCN_TO_TIMESTAMP(ora_rowscn)>=(select starttime from table2 where taskid=10502) and SCN_TO_TIMESTAMP(ora_rowscn)<=(select finishtime from table2 where taskid=10502)
The above query is working but it is very slow. So, I have tried another query which is not working
delete from table1 where SCN_TO_TIMESTAMP(ora_rowscn) in (select starttime, finishtime from table2 where taskid=10502);
I have searched on Stack Overflow, but I didn't get anything. Could anyone please help me in making a better query?