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

Question

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?

Answer 1

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.

• Jay: Thank you for your response, I just tried the CTE Query you mentioned, but it didn't work with the delete statement. I tried replacing the delete with select keyword and it is working well in that case. ` WITH CTE_TasksToDelete AS (select starttime, finishtime, taskid from table2 where taskid=10502) SELECT FROM table1 WHERE SCN_TO_TIMESTAMP(ora_rowscn) BETWEEN ( SELECT starttime FROM CTE_TasksToDelete) AND (SELECT finishtime FROM CTE_TasksToDelete); `

Answer 2

At the very least, the statement can be rewritten to avoid hitting table2 twice. For that, you can use an EXISTS predicate:

DELETE FROM
 table1
WHERE
 EXISTS
 (
 SELECT
 *
 FROM
 table2
 WHERE
 table2.taskid = 10502
 AND SCN_TO_TIMESTAMP(table1.ORA_ROWSCN) BETWEEN table2.starttime AND table2.finishtime
 )
;

In addition to that, instead of converting ORA_ROWSCN to a timestamp and testing the result against starttime and finishtime, it would make sense to at least try the other way round: convert starttime and finishtime to SCN and test table1's ORA_ROWSCN directly against the resulting range:

SELECT
 *
FROM
 table1
WHERE
 EXISTS
 (
 SELECT
 *
 FROM
 table2
 WHERE
 table2.taskid = 10502
 AND table1.ORA_ROWSCN BETWEEN TIMESTAMP_TO_SCN(table2.starttime)
 AND TIMESTAMP_TO_SCN(table2.finishtime)
 )
;

A covering index on table2 (taskid, starttime, finishtime) might also be helpful with either variation.