Oracle 大量数据有条件删除

[nomic]

各位救命，Oracle在有条件删除的情况下咋那么慢啊，有什么好的practice没有？

删除SQL如下, 简单的逻辑是，删除同一天中（字段eff_frm_gmt_ts）不是最后更新的记录，并且只删除6个月以前的数据。

这个表中有191,513,804条记录,占用大概248G磁盘空间，允许下面删除语句6个多小时没有结果。

DELETE --sco.so_lc ou
--SELECT
-- COUNT(*)
FROM
SCO.SO_LC OU
WHERE
(
ou.so_id ,ou.src_sys_ky ,ou.so_ln_itm_id ,ou.sch_ln_itm_id ,ou.eff_frm_gmt_ts) IN
(
SELECT
so_id ,
src_sys_ky ,
so_ln_itm_id ,
sch_ln_itm_id ,
eff_frm_gmt_ts
FROM
(
SELECT
/*+ PARALLEL (i, 500)*/
i.so_id ,
i.src_sys_ky ,
i.so_ln_itm_id ,
i.sch_ln_itm_id ,
i.eff_frm_gmt_ts ,
ROW_NUMBER () OVER ( PARTITION BY i.so_id ,i.src_sys_ky ,i.so_ln_itm_id ,
i.sch_ln_itm_id ,TO_CHAR (i.eff_frm_gmt_ts ,'MMDDYYYY') ORDER BY
i.eff_frm_gmt_ts DESC) rn
FROM
sco.so_lc i
WHERE
i.eff_frm_gmt_ts < ADD_MONTHS (SYSDATE, -6))
WHERE
rn > 1)
AND ou.eff_frm_gmt_ts < ADD_MONTHS (SYSDATE, -6);

[lhdz_bj]

1、你这是删除大部分记录，保留小部分记录。 2、先把每天最后更新的记录保存到其他表里去，然后，删除6个月前的所有记录。 3、再把保存到其他表里的每天最后更新的记录insert回原表中。 如果分区表了，就更好办了。

[nomic]

引用 1 楼 LHDZ_BJ 的回复:

1、你这是删除大部分记录，保留小部分记录。 2、先把每天最后更新的记录保存到其他表里去，然后，删除6个月前的所有记录。 3、再把保存到其他表里的每天最后更新的记录insert回原表中。 如果分区表了，就更好办了。

多谢建议，但是即使删除6个月前的记录也是时间非常长，6个小时没结果的。可能可以先把6个月内和6月前每天最后更新放到临时表里，trunc掉原表，然后insert回去。但是这个删除任务是每周要运行一次，而且在生产环境不允许在任务脚本里创建删除表。 你的思路给了些启发，感谢 DELETE --sco.so_lc ou --SELECT -- COUNT(*) FROM SCO.SO_LC OU WHERE ou.eff_frm_gmt_ts < ADD_MONTHS (SYSDATE, -6);

[lhdz_bj]

其实，只是粗略的建议，没看到执行计划，如果方便，贴下计划吧，我帮你看看。 这种表，还是建议分区。

[nomic]

真是感谢，我两个问题都是你在帮我分析，太感谢了。如果在上海一定要请你吃饭。 Plan hash value: 74112572 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | DELETE STATEMENT | | 171M| 29G| | 1151K (1)| 03:03:15 | | | | | 1 | DELETE | SO_LC | | | | | | | | | | 2 | PX COORDINATOR | | | | | | | | | | | 3 | PX SEND QC (RANDOM) | :TQ10002 | | | | | | Q1,02 | P->S | QC (RAND) | | 4 | NESTED LOOPS | | | | | | | Q1,02 | PCWP | | | 5 | NESTED LOOPS | | 171M| 29G| | 1151K (1)| 03:03:15 | Q1,02 | PCWP | | | 6 | VIEW | VW_NSO_1 | 171M| 19G| | 7906 (2)| 00:01:16 | Q1,02 | PCWP | | | 7 | SORT UNIQUE | | 171M| 8169M| 10G| 7906 (2)| 00:01:16 | Q1,02 | PCWP | | | 8 | PX RECEIVE | | 171M| 8169M| | 3871 (2)| 00:00:37 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 171M| 8169M| | 3871 (2)| 00:00:37 | Q1,01 | P->P | HASH | |* 10 | VIEW | | 171M| 8169M| | 3871 (2)| 00:00:37 | Q1,01 | PCWP | | | 11 | WINDOW SORT | | 171M| 6045M| 8525M| 3871 (2)| 00:00:37 | Q1,01 | PCWP | | | 12 | PX RECEIVE | | 171M| 6045M| | 3839 (1)| 00:00:37 | Q1,01 | PCWP | | | 13 | PX SEND HASH | :TQ10000 | 171M| 6045M| | 3839 (1)| 00:00:37 | Q1,00 | P->P | HASH | | 14 | PX BLOCK ITERATOR | | 171M| 6045M| | 3839 (1)| 00:00:37 | Q1,00 | PCWC | | |* 15 | TABLE ACCESS FULL| SO_LC | 171M| 6045M| | 3839 (1)| 00:00:37 | Q1,00 | PCWP | | |* 16 | INDEX UNIQUE SCAN | SO_LC_XPK | 1 | | | 2 (0)| 00:00:01 | Q1,02 | PCWP | | |* 17 | TABLE ACCESS BY INDEX ROWID| SO_LC | 1 | 64 | | 3 (0)| 00:00:01 | Q1,02 | PCWP | | ---------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 10 - filter("RN">1 AND "EFF_FRM_GMT_TS"<ADD_MONTHS(SYSDATE@!,-6)) 15 - filter("I"."EFF_FRM_GMT_TS"<ADD_MONTHS(SYSDATE@!,-6)) 16 - access("OU"."EFF_FRM_GMT_TS"="EFF_FRM_GMT_TS" AND "OU"."SO_ID"="SO_ID" AND "OU"."SO_LN_ITM_ID"="SO_LN_ITM_ID" AND "OU"."SCH_LN_ITM_ID"="SCH_LN_ITM_ID") filter("OU"."EFF_FRM_GMT_TS"<ADD_MONTHS(SYSDATE@!,-6)) 17 - filter("OU"."SRC_SYS_KY"="SRC_SYS_KY")

[nomic]

好像做删除的时候有index反而会是删除更慢，我这个还没有disable index

[lhdz_bj]

把parallel hint去掉，在用use_hash试试，如下： DELETE /*+ use_hash(OU)*/ FROM SCO.SO_LC OU。。。（省去其他部分，麻烦自己填写）。 再不行，改成merge into语句试试。

[eva_1st]

数据太多,还是最好用分区表吧. 之前我也遇到同样的问题.最后狠心停止业务,导出数据,建分区表.再导回去. 现在清理数据是轻松的不能再轻松了.

[lhdz_bj]

分区表不一定要停业务，在线分区也可以，只是要在负载低谷做比较好。

[nomic]

引用 7 楼 eva_1st 的回复:

数据太多,还是最好用分区表吧. 之前我也遇到同样的问题.最后狠心停止业务,导出数据,建分区表.再导回去. 现在清理数据是轻松的不能再轻松了.

我了解下来，确实分区是最有效和长久的方法，可是我们DBA不大愿意。

[铁铲]

248G的数据 帮顶