【请高手指教！】夜维删除多个表小部分(1/10的百万级)记录的案例

Question

本帖最后由 bisal 于 2013-09-28 23:42:09 编辑

Answer 1

主表按EXPIRE_DATE每天建立分区，删除的时候 直接 drop partition 。子表的LOCATOR_ID 建立索引。将3天的 主表LOCATOR_ID 查出来放在临时表后，关联删除子表对应的数据。
然后Procedure中循环依次执行这10几张表，删除子表中存在该LOCATOR_ID的记录：
--用1个过程太慢的话，，把10几个子表分开用几个过程同时执行

Answer 2

方案2就可以了，子表直接删除，最后删除主表。
DELETE FROM yourTable WHERE EXPIRE_DATE < TRUNC(SYSDATE) - 3;几百万条数据不需要分区。
日期转换应该是为了去掉时间部分，用trunc可以达到同样的效果，column上面不要加函数。

Answer 3

一个旧应用，有一张主表ANA，200-300万数据，还有10几个字表，200-300万数据，每张表都有LOCATOR_ID主键字段，主表有一个EXPIRE_DATE时间字段。需求：每天夜维删除所有表中历史3天前的数据(由于业务的关系，例如今天删除了3天前的数据，明天可能又有3天前的数据插入)，3天前的历史数据大约30万左右。原来的做法：
先从主表中找到历史3天前的所有LOCATOR_ID，创建一个中间表：
INSERT INTO TEMP (LOCATOR_ID,DESCRIBE,OPERATE_TIME)
(SELECT A.LOCATOR_ID,'夜维待处理数据，过期时间：' || TO_CHAR(EXPIRE_DATE,'YYYY-MM-DD HH24:MI:SS') DESCRIBE,SYSDATE AS OPERATE_TIME FROM ANA A WHERE  CAST(A.EXPIRE_DATE AS DATE) < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - 3);然后Procedure中循环依次执行这10几张表，删除子表中存在该LOCATOR_ID的记录：
DELETE FROM ANA_SEG A WHERE EXISTS (SELECT 1 FROM (SELECT LOCATOR_ID FROM (SELECT T.LOCATOR_ID,ROWNUM RN FROM TEMP T ORDER BY T.LOCATOR_ID) WHERE RN > 0 AND RN <= 
50000) B WHERE A.LOCATOR_ID = B.LOCATOR_ID);(感觉使用EXIST要比使用IN：delete from ANA_SEG where locator_id in (select locator_id from temp);高效，因为EXIST判断的是存在性，IN判断的是匹配性)
...
每5万条删除一次。但这样做的效率很低，每天删除都在4-5个小时，有几次因为夜维执行失败，积累了几天的数据，100万要删除的，执行时超过一天也没执行完成。
于是需要作出优化操作。想到的方案：
方案1：
1、将中间表TEMP修改为全局临时表(SESSION级)。
2、删除每个子表时使用HASH JOIN方式(因为这里中间表也有10几万，所以现在的Nested Loop方式效率可能不高)非嵌套查询的方式执行DELETE。
DELELE FROM ANA_SEG A, TEMP B WHERE B.LOCATOR_ID = A.LOCATOR_ID;
每个表依次执行，用程序(例如PROC或Java)实现计数器，保证每50000条执行一次COMMIT。方案2：
不用中间表，还是每次执行子表利用SYSDATE-EXPIRE_DATE>3+ROWNUM<50000的条件DELETE，(不太明白用WHERE  CAST(A.EXPIRE_DATE AS DATE) < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - 3)转换日期格式的目的是什么，感觉可以不用这么做)。这样做的不好之处就是日期字段没有索引，没各字表全表扫描，优点就是不用执行这么多的嵌套查询。
--删除此方案，因为发现有些子表是没有EXPIRE_DATE字段的。方案3：
与方案2类似，不同的是先从各个子表找出SYSDATE-EXPIRE_DATE>3符合条件的记录rowid(使用APPEND方式INSERT 中间表或临时表)，存入一个nologing的临时中间实体表(或者commit级别的temporary临时表，效果应该差不多？)，然后DELETE每个子表依次执行，以及ROWNUM<50000的条件，目的就是通过ROWID删除可以具体定位到记录。
--删除此方案，因为发现有些子表是没有EXPIRE_DATE字段的。方案4：
1、将所有子表设计为分区表，例如1个月1个分区。
2、采用方案2的方式执行删除，因为是以时间为分区条件，因此SYSDATE-EXPIRE_DATE>3+ROWNUM<50000的条件可以到某一个或两个分区中操作，数量较整体会少很多。月初几天可能扫描两个月的分区。
--因为发现有些子表是没有EXPIRE_DATE字段的，如果使用分区表，只能是主表，只有生成中间表的过程可以得到分区表的益处。看来必须生成中间表，然后在各个子表中找与之匹配的LOCATOR_ID进行DELETE，10几个表可以考虑是否并行操作，另外还有个重要的问题，就是当前数据库的存储较旧，IO缓慢，需要过段时间才能更换存储请高手指点以上方案是否合理？或者是否有更佳的方案？谢谢！

Answer 4

新了解的情况，与上面有点区别，但原帖已经不让编辑了，请见如下，谢谢！一个旧应用，有一张主表ANA，200-300万数据，还有10几个字表，200-300万数据，每张表都有LOCATOR_ID主键字段，主表有一个EXPIRE_DATE时间字段。需求：每天夜维删除所有表中历史3天前的数据(由于业务的关系，例如今天删除了3天前的数据，明天可能又有3天前的数据插入)，3天前的历史数据大约30万左右。原来的做法：
先从主表中找到历史3天前的所有LOCATOR_ID，创建一个中间表：
INSERT INTO TEMP (LOCATOR_ID,DESCRIBE,OPERATE_TIME)
(SELECT A.LOCATOR_ID,'夜维待处理数据，过期时间：' || TO_CHAR(EXPIRE_DATE,'YYYY-MM-DD HH24:MI:SS') DESCRIBE,SYSDATE AS OPERATE_TIME FROM ANA A WHERE  CAST(A.EXPIRE_DATE AS DATE) < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - 3);然后Procedure中循环依次执行这10几张表，删除子表中存在该LOCATOR_ID的记录：
DELETE FROM ANA_SEG A WHERE EXISTS (SELECT 1 FROM (SELECT LOCATOR_ID FROM (SELECT T.LOCATOR_ID,ROWNUM RN FROM TEMP T ORDER BY T.LOCATOR_ID) WHERE RN > 0 AND RN <= 
50000) B WHERE A.LOCATOR_ID = B.LOCATOR_ID);(感觉使用EXIST要比使用IN：delete from ANA_SEG where locator_id in (select locator_id from temp);高效，因为EXIST判断的是存在性，IN判断的是匹配性)
...
每5万条删除一次。但这样做的效率很低，每天删除都在4-5个小时，有几次因为夜维执行失败，积累了几天的数据，100万要删除的，执行时超过一天也没执行完成。
于是需要作出优化操作。想到的方案：
方案1：
1、将中间表TEMP修改为全局临时表(SESSION级)。
2、删除每个子表时使用HASH JOIN方式(因为这里中间表也有10几万，所以现在的Nested Loop方式效率可能不高)非嵌套查询的方式执行DELETE。
DELELE FROM ANA_SEG A, TEMP B WHERE B.LOCATOR_ID = A.LOCATOR_ID;
每个表依次执行，用程序(例如PROC或Java)实现计数器，保证每50000条执行一次COMMIT。方案2：
不用中间表，还是每次执行子表利用SYSDATE-EXPIRE_DATE>3+ROWNUM<50000的条件DELETE，(不太明白用WHERE  CAST(A.EXPIRE_DATE AS DATE) < TO_DATE(TO_CHAR(SYSDATE,'YYYY-MM-DD'),'YYYY-MM-DD') - 3)转换日期格式的目的是什么，感觉可以不用这么做)。这样做的不好之处就是日期字段没有索引，没各字表全表扫描，优点就是不用执行这么多的嵌套查询。
--删除此方案，因为发现有些子表是没有EXPIRE_DATE字段的。方案3：
与方案2类似，不同的是先从各个子表找出SYSDATE-EXPIRE_DATE>3符合条件的记录rowid(使用APPEND方式INSERT 中间表或临时表)，存入一个nologing的临时中间实体表(或者commit级别的temporary临时表，效果应该差不多？)，然后DELETE每个子表依次执行，以及ROWNUM<50000的条件，目的就是通过ROWID删除可以具体定位到记录。
--删除此方案，因为发现有些子表是没有EXPIRE_DATE字段的。方案4：
1、将所有子表设计为分区表，例如1个月1个分区。
2、采用方案2的方式执行删除，因为是以时间为分区条件，因此SYSDATE-EXPIRE_DATE>3+ROWNUM<50000的条件可以到某一个或两个分区中操作，数量较整体会少很多。月初几天可能扫描两个月的分区。
--因为发现有些子表是没有EXPIRE_DATE字段的，如果使用分区表，只能是主表，只有生成中间表的过程可以得到分区表的益处。看来必须生成中间表，然后在各个子表中找与之匹配的LOCATOR_ID进行DELETE，10几个表可以考虑是否并行操作，另外还有个重要的问题，就是当前数据库的存储较旧，IO缓慢，需要过段时间才能更换存储请高手指点以上方案是否合理？或者是否有更佳的方案？谢谢！

Answer 5

参考一楼的，不过我觉得你需要事先分析一下生成临时表的时间，以及删除消耗的时间才能知道瓶颈在哪。另外建议不要用临时表，而直接新建一表存储这些需要删除的locator_id，删除结束后下次删除之前truncate掉。

Answer 6

“参考一楼的，不过我觉得你需要事先分析一下生成临时表的时间，以及删除消耗的时间才能知道瓶颈在哪。”
瓶颈应该在于DELETE子表的操作。
Execution Plan
----------------------------------------------------------
Plan hash value: 3752040547-------------------------------------------------------------------------------------------------| Id  | Operation               | Name          | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |               |   225K|    15M|       | 18309  (1)| 00:03:40 |
|*  1 |  HASH JOIN RIGHT SEMI   |               |   225K|    15M|  4168K| 18309  (1)| 00:03:40 |
|*  2 |   VIEW                  |               |   133K|  2604K|       |    88  (2)| 00:00:02 |
|   3 |    COUNT                |               |       |       |       |     |          |
|   4 |     INDEX FAST FULL SCAN| P_A                |   133K|   911K|       |    88  (2)| 00:00:02 |
|   5 |   TABLE ACCESS FULL     | B                |  3598K|   171M|       |  7448  (1)| 00:01:30 |
-------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   1 - access("A"."ID"="ID")
   2 - filter("RN">0 AND "RN"<=50000)Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      27479  consistent gets
          0  physical reads
          0  redo size
      11651  bytes sent via SQL*Net to client
        645  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        168  rows processed“另外建议不要用临时表，而直接新建一表存储这些需要删除的locator_id，删除结束后下次删除之前truncate掉。”
这样做的目的是什么呢？我理解用temporary不是可以产生更少的redo，而且临时表的数据不需要保存的，您说不建立临时表是出于什么考虑的呢？谢谢！

Answer 7

临时表空间导致的瓶颈问题，因为会有十几万条数据insert。可以做一个测试比较一下这两种方式。