带有主外键关系的父表数据滤重

问题分析：

这是一个真实的生产洗数据操作，生产洗数据就是耍杂技，需谨慎！谨慎！！谨慎！！！

表CHANNEL_MERCHANT主键列为ID调用sequence生成
唯一性列CARD缺失唯一性约束，造成该列数据重复
该表是父表，主键列ID被表CHANNEL_TRANSATION列MERCHANTID参照

处理步骤：

1° 父表CARD列添加唯一性约束
添加唯一性约束，延迟生效，使新入库数据不存在问题

alter table CHANNEL_MERCHANT
add constraint uq_cm_card unique (CARD)
using index deferrable enable novalidate;

2° 查询出父表的所有依赖子表
要保证所有的子表参照数据都修改后，父表才能滤重
因此需要验证只有一个子表外键参照关系

select f.OWNER || '.' || f.TABLE_NAME || '.' || f.COLUMN_NAME father_col,
       c.OWNER || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME children_col
  from user_cons_columns f, user_cons_columns c
 where (f.CONSTRAINT_NAME, c.CONSTRAINT_NAME) in
       (select child.R_CONSTRAINT_NAME father_cons_name,
               child.CONSTRAINT_NAME   children_cons_name
          from user_constraints father, user_constraints child
         where father.TABLE_NAME = upper('channel_merchant')
           and father.CONSTRAINT_TYPE in ('P', 'U')
           and father.CONSTRAINT_NAME = child.R_CONSTRAINT_NAME);

3° 查看子表外键约束是否缺失索引
如果外键缺失索引，当父表或者子表数据较多时，性能急剧下降
因此洗数据要先保证外键索引存在

select c.TABLE_NAME, c.COLUMN_NAME, c.INDEX_NAME
  from user_ind_columns c
 where c.TABLE_NAME = 'CHANNEL_TRANSATION'
   and c.COLUMN_NAME = 'MERCHANTID';

4° 生成子表update和父表delete语句

select 'update CHANNEL_TRANSATION set MERCHANTID=' ||
       regexp_substr(ids, '[^,]+', 1, 1) || ' where MERCHANTID in (' ||
       replace(ids, regexp_substr(ids, '[^,]+', 1, 1) || ',', '') || ');' ||
       chr(10) || 'delete from channel_merchant where id in (' ||
       replace(ids, regexp_substr(ids, '[^,]+', 1, 1) || ',', '') || ');' ||
       chr(10) || 'commit;' as FIN_DML
  from (select card, wm_concat(id) ids
          from channel_merchant
         group by card
        having count(*) > 1);
-- 该SQL比较复杂，相关解释如下：
-- from子句根据card分组，使用函数wm_concat将id以逗号为分隔符聚合，生成ids
-- select子句拼update和delete以及commit语句，以chr(10)拼出换行符
-- regexp_substr(ids, '[^,]+', 1, 1) 是将ids以逗号为分隔符取出第一列
-- replace(ids, regexp_substr(ids, '[^,]+', 1, 1) 将ids的逗号分隔的第一列替换成空
-- 最后生成的update语句：update t set col='ids第一列' where col in ('ids 去掉第一列');
-- 最后生成的delete语句：delete from t where col in ('ids 去掉第一列');

5° 父表唯一性约束取消延迟生效功能

alter table CHANNEL_MERCHANT enable validate constraint uq_cm_card;

6° 收尾
生产具体操作时还需要考虑业务负载、父子表负载、父子表是否有相关联的统计报表SQL
是否存在非库级主外键依赖，洗数据操作是否对这些依赖产生影响
总之是这一对父子表相关的方方面面和库的当前情况
因此生产洗数据就是耍杂技，搞不好就会摔伤自己，骨折瘫痪都是有可能的 …
找个小本本记下该问题，找机会逮住开发可劲儿怼~

[TOC]