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.

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.