Oracle: Delete data from one table based on starttime and finishtime column of another table

oracle

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?

Best Answer

In your second query, your where clause has 1 column trying to compare to TWO columns in your subquery. That's probably the error you're getting.

Have you tried using CTE? http://www.dba-oracle.com/t_common_table_expression_cte.htm

However, I think your REAL problem is that you're using a function (SCN_TO_TIMESTAMP) in your where clause, and it's ignoring any indexing on the ora_rowscn column.

Here's the CTE anyway, but I doubt it will be much of a performance improvement.

WITH CTE_TasksToDelete
AS
(select starttime, 
        finishtime,
        taskid --Best if you're going to be using multiple taskIDs 
 from table2 
 where taskid=10502)
DELETE FROM table1
WHERE SCN_TO_TIMESTAMP(ora_rowscn) BETWEEN ( SELECT starttime 
                                             FROM CTE_TasksToDelete)
                                   AND (SELECT finishtime
                                        FROM CTE_TasksToDelete);

If anyone else wants to edit this and make it better, feel free. Just wanted to give a starting point.

Again, I'm fairly certain your issue is that SCN_TO_TIMESTAMP function. Find a way to make that an actual column in your table1, then index it, and you'll be a happy camper. Also, batch your deletes friend.

Related Question