Oracle：根據另一張表的開始時間和結束時間列從一個表中刪除數據

我想刪除在特定時間範圍之間插入的 Table1 中的所有數據。我從 Table2 列starttime和finishtime獲得的時間範圍值，這兩個表之間沒有關係。因此，為此，我編寫了以下查詢：

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)

上面的查詢正在工作，但速度很慢。所以，我嘗試了另一個不起作用的查詢

delete from table1 where 
SCN_TO_TIMESTAMP(ora_rowscn) in 
(select starttime, finishtime from table2 where taskid=10502);

我在 Stack Overflow 上進行了搜尋，但沒有得到任何資訊。誰能幫我做一個更好的查詢？

在您的第二個查詢中，您的 where 子句有 1 列試圖與子查詢中的兩列進行比較。這可能是你得到的錯誤。

您是否嘗試過使用 CTE？ http://www.dba-oracle.com/t_common_table_expression_cte.htm

但是，我認為您真正的問題是您在 where 子句中使用了一個函式 (SCN_TO_TIMESTAMP)，它忽略了 ora_rowscn 列上的任何索引。

無論如何，這是 CTE，但我懷疑它會大大提高性能。

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);

如果其他人想編輯它並使其變得更好，請隨意。只是想給出一個起點。

同樣，我很確定您的問題是 SCN_TO_TIMESTAMP 函式。找到一種方法使該列成為表中的實際列1，然後對其進行索引，您將成為一個快樂的露營者。另外，批量刪除您的朋友。

至少，該語句可以重寫以避免table2兩次命中。為此，您可以使用EXISTS謂詞：

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
 )
;

除此之外，與其轉換ORA_ROWSCN為時間戳並針對 and 測試結果starttime，finishtime不如至少嘗試相反的方式：將andstarttime轉換finishtime為 SCN 並直接針對結果範圍進行測試：table1``ORA_ROWSCN

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)
 )
;

覆蓋索引table2 (taskid, starttime, finishtime)也可能對任何一種變化都有幫助。

引用自：https://dba.stackexchange.com/questions/201978